topic Re: distinct row count in QlikView

distinct row count

Wed, 23 Jul 2014 07:44:29 GMT

below is the sample table

CAL:

Load * INLINE [

days,no

Jan,1

Feb,1

Mar,3

Apr,2

May,4

Jun,4

Jul,5

Aug,6

Sep,7

Oct,8

Nov,9

Dec,5

];

i want to get thhe distinct count of column "no"

at script level i am using

let vvar = count(DISTINCT no);

trace >>> countof: $(vvar);

but log file show null

plz provide any solution for the same the required answer should be "9"

Re: distinct row count

its_anandrjs — Wed, 23 Jul 2014 07:54:17 GMT

Try this way

CAL:

LOAD * INLINE [

days, no

Jan, 1

Feb, 1

Mar, 3

Apr, 2

May, 4

Jun, 4

Jul, 5

Aug, 6

Sep, 7

Oct, 8

Nov, 9

Dec, 5

];

//Add this table for distinct count

Cnt:

LOAD

count(DISTINCT no) as No

Resident CAL;

let vvar = Peek('No',0,'Cnt'); //And store in this variable

trace >>> countof: $(vvar);

Regards

Re: distinct row count

Wed, 23 Jul 2014 08:17:46 GMT

why we need to create a new table (i.e Cnt)?, why it is not possible with "count" command directly

Re: distinct row count

sujeetsingh — Wed, 23 Jul 2014 08:20:03 GMT

You can easily get this done in the UI.

For script just Anand has provided a sample solution.

Re: distinct row count

its_anandrjs — Wed, 23 Jul 2014 08:29:24 GMT

If manually you create the variable in the front end then you can easily achieve with expression

vvar = count(DISTINCT no);

But in load script you have to refer the table directly.

Re: distinct row count

sujeetsingh — Wed, 23 Jul 2014 09:16:26 GMT

That is from UI