topic Re: Count the values in QlikView

Count the values

santharubban — Mon, 18 Aug 2014 16:28:16 GMT

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.

Re: Count the values

Gysbert_Wassenaar — Mon, 18 Aug 2014 17:26:00 GMT

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.

Re: Count the values

santharubban — Mon, 18 Aug 2014 17:35:21 GMT

thanks for reply can you explain the crosstable function please.

Re: Count the values

Gysbert_Wassenaar — Mon, 18 Aug 2014 17:39:27 GMT

See The Crosstable Load

Re: Count the values

santharubban — Mon, 18 Aug 2014 20:06:22 GMT

i need distinct value for 10 columns

Re: Count the values

jagan — Tue, 19 Aug 2014 02:41:49 GMT

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.