topic Re: For and while loop. in QlikView

For and while loop.

brindlogcool — Thu, 05 Jul 2012 03:15:08 GMT

Hi,

I am trying to generate the dates using the for loop and while loop .But i am getting the error. Is it possible to use the loop in the below way.

For i=1 to NoOfRows('TESTFACT')

Let vid=FieldValue('ID',$(i));
Let vDateMin=FieldValue('STRT_TIME',$(i));
Let vDateMax=FieldValue('END_TIME',$(i));

DGEN:
LOAD
$(vid) as ID,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Next i

Thanks for the help

Re: For and while loop.

Anonymous — Thu, 05 Jul 2012 03:21:09 GMT

Sounds like you will get a loop with a lot of iterations if you loop through this for every row of your TESTFACT table.

Why don't you just set the vDateMin and vDateMax to the min STRT_TIME and max END_TIME and just generate all the dates once?

Re: For and while loop.

brindlogcool — Thu, 05 Jul 2012 04:05:17 GMT

Hi,

Ya i agree with you. But i havent find any solution for my problem. Actual problem is to split the dates within range in to individual dates . For example

Test1 100 10/6/2011 11/6/2011

Test2 150 11/6/2011 15/6/2011

Test3 80 15/6/2011 17/6/2011

and I want to loop the table and create a new table with as below

Test1 100 10/6/2011

Test1 100 11/6/2011

Test2 150 11/6/2011

Test2 150 12/6/2011

Test2 150 13/6/2011

Test2 150 14/6/2011

Test2 150 15/6/2011

Test3 80 15/6/2011

Test3 80 16/6/2011

Test3 80 17/6/2011

I tried to use the interval match but it is generating the duplicate values .Refer the below link. So i was trying to loop through each row. Appreciate your help.

http://community.qlik.com/message/228361#228361

Re: For and while loop.

Thu, 05 Jul 2012 04:23:03 GMT

Hi,

Is this what you are looking for?

tempTable:
LOAD * INLINE [
    TestId, Amount, StartDate, EndDate
    Test1, 100, 10/06/2011, 11/06/2011
    Test2, 150, 11/06/2011, 15/06/2011
    Test3, 80, 15/06/2011, 17/06/2011
];

table:
LOAD
TestId,
Amount,
Date(StartDate+IterNo()-1)
Resident tempTable
while Date(StartDate+IterNo()-2)<Date(EndDate);

Drop table tempTable;

Regards,

Xue Bin

Re: For and while loop.

Anonymous — Thu, 05 Jul 2012 04:23:23 GMT

I agree with you, I think the calendar + intervalmatch() is a better option.

Looking at the example you provided in the other thread, I think you'll get the expected result using a distinct qualifier in the intervalmatch load:

FullTable:

JOIN (Data) INTERVALMATCH (Date) LOAD distinct DateStart, DateEnd

RESIDENT Data;

Re: For and while loop.

brindlogcool — Thu, 05 Jul 2012 04:39:55 GMT

Thanks Johannes for fixing the Interval match.It was really helpful. I will validate my data once and discard the loop .

Also thanks Xue Bin for your response.

Re: For and while loop.

brindlogcool — Thu, 05 Jul 2012 04:43:17 GMT

Hi Johannes, I am not getting the Option Helpful to mark the answer as Helpful. Any idea why it is not showing?

Re: For and while loop.

Anonymous — Thu, 05 Jul 2012 05:00:50 GMT

Not sure man.. but thanks for the appreciation