Count the values

[santharubban]

I need to distinct count for values. 10 different column each colunm has its own value, if col 1 value and any col have same value then it should be counted as distinct. example pls referre the attachement.

[Gysbert_Wassenaar]

Maybe like this:

count(distinct valuelist($(=concat(DIAG_01_CD,',') & ',' & $(=concat(DIAG_02_CD,',')  & ',' & $(=concat(DIAG_03_CD,',') & ',' & $(=concat(DIAG_04_CD,',') & ',' & $(=concat(DIAG_05_CD,',') & ',' & $(=concat(DIAG_06_CD,',') & ',' & $(=concat(DIAG_07_CD,',') & ',' & $(=concat(DIAG_08_CD,',') & ',' & $(=concat(DIAG_09_CD,',') & ',' & $(=concat(DIAG_10_CD,',') ))

Or perhaps you should use the Crosstable function to transform the data first into a format that is easier to work with.

[santharubban]

thanks for reply can you explain the crosstable function please.

[Gysbert_Wassenaar]

See The Crosstable Load

[santharubban]

i need distinct value for 10 columns

[jagan]

Hi,

Try like this in script

Data:

CrossTable(Field, Value)

LOAD

  RecNo() AS RowNo,

  Field1,

  Field2,

  Field3

INLINE [   

    Field1, Field2, Field3

    8052, 9000, 2000

    1000, 2000, 8052

    9000, 8052, 1000

];

Now in expression try like this

=Sum(Aggr(If(Count(Value) > 1,1), Value))

Regards,

Jagan.