count unique values ??between duplicates in X++ or Visual Studio

(0) Share

Report

Posted on by Community Member

Good afternoon, there is a way to add unique values (in the table repeat some of these values) in a table so that when designing the ssrs report in Visual Studio add the values that are really and do not add all with duplicates (already that would not be the correct amount) Thanks in advance

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Jie G on at

Like (1)

Report

Hi Carolina,

Do you mean selecting the distinct values from the table? e.g.: Select distinct id from table....

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ada. Thank you for answering me, I explain, for example in a table I have the following values: 36, 45, 79, 36, 65, 76, 79, 36.

The 36 and 79 are repeated, right?

Well, I want you to join me once only on the 36th and once only on the 79th plus the other values that would be 45, 65,76. You understand me?

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (1)

Report

Hi,

if your table is mytable and the column where you have multiple entries is groupcol, you can do the following:

- define, WHICH record on the duplicates is the one you want to have as 'first' and 'always'

let's define 'the first' as the one with the highest recid in this example

- create a view helperview with column groupcol and aggregate maxof(recid)

this view will contain the distinct groupcol valuey with the highest occuring recid

- create another view selecting all fields from mytable inner joined to the helperview (groupcol, recid) and use this view or

- simply inner or exist join the helperview to your table mytable select.

This will remove the all the 'non first' records

Regards Douglas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Douglas

Thank you very much for your response, I was somewhat confused, I ask you a question, in my table there are temporary values that are repeated (nomic values) because I need an amount of each category of products and this I repeat in every sale that is made with that category , I explain better as I put in the example above, it would be something like this:

Categories: Field1

Minimum amount to seller: Campo2

Table:

Field1 Field2

Categories Minimum amount a seller

Accessories 36

bottles 69

Consumables 23

bottles 69

Fabrics 21

Several 90

Accessories 36

How would you do in the design (Visual Studio) or X++ so that only the unique values (not duplicates) would be: 36 (one time only) + 69 (one time only) + 23 + 21 + 90?

Was this reply helpful? Yes No
Verified answer

Douglas Noel 3,905 on at

Like (1)

Report

Ok,

it seems that field2 has always the same values for a given field1, but this doesn't care:

Even if you have different field2 values for a given field1 (bottles with 69 and 68) you can select the minimum

E.g. disinct select of field1 and the minimum(if all values are the same this is also the minimum)

while select field1, minof(field2) from xyztable

group by field1

{

// info(strfmt('%1 - %2', xyztable.field1, xyztable.field2));

}

you can do the same with a query (bound to a formdatasorce). In that case you should clear all fields from selection, add field1 as group field, field2 as aggreation field with type minof

Other way would be simply skip insertions of duplicates in your temp. table maybe using some logic which checks the existance of a given field1 value and skips insertion in that case.

But I don't know anything about your underlaying requirements and used objects, so it's hard to say what the best way is.

Regards Douglas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you very much!

Was this reply helpful? Yes No