For and while loop.

[brindlogcool]

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

[Anonymous]

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?

[brindlogcool]

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

[#4]

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

[Anonymous]

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;

[brindlogcool]

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.

[brindlogcool]

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

[Anonymous]

Not sure man.. but thanks for the appreciation