topic Re: Loop through distinct count in the Script in QlikView

Loop through distinct count in the Script

sunny_talwar — Mon, 06 Oct 2014 18:53:20 GMT

Hi All,

I am trying to use For loop using a variable which has been assigned a value using count function:

Let vLoop = '=Count(DISTINCT abc)'

For i = $(vLoop) to 60

...

Next i

I have tried with different combinations of Let and Set but none of them seems to work.

Let vLoop = 'Count(DISTINCT abc)'

Let vLoop = Count(DISTINCT abc)

Set vLoop = '=Count(DISTINCT abc)'

Set vLoop = 'Count(DISTINCT abc)'

Set vLoop = Count(DISTINCT abc)

Any ideas on what might I be doing wrong here.

Thanks,

maxgro — Mon, 06 Oct 2014 19:31:08 GMT

is abc a field?

EDIT

if yes try with

load * inline [

abc

];

tmp: load count (distinct abc) as distinctabc resident t;

let v = peek('distinctabc');

for i = v to 60

trace $(i);

next;

Mon, 06 Oct 2014 19:31:15 GMT

Hi,

You'll need to use the Peek function in the script to assign the value to the variable.

Try like this, after loading your initial table (let's say it's labeled T_Data):

T_Count:

Load count(distinct abc) as Cnt

resident T_Data;

let vLoop = peek('Cnt', 0, 'T_Count');

drop table T_Count;

for i = $(vLoop) to 60

...

sunny_talwar — Mon, 06 Oct 2014 19:56:04 GMT

Yes it was one of a field. Thanks your solution worked out.

Best,