Discussion Board for collaboration on QlikView Scripting.
Hi all, I'm new to clikview so this might be a silly question... In any case here it goes:
I have an Excel file with four fields: Employee, Date_start, Date_end, Number_of_days
Nick 1/1/2013 2/1/2013 2John 9/1/2013 11/1/2013 3Nick 19/1/2013 21/1/2013 3
I want to create a new table containing all the dates of the corresponding range:
Nick 1/1/2013Nick 2/1/2013John 9/1/2013 John 10/1/2013 John 11/1/2013 Nick 19/1/2013 Nick 20/1/2013 Nick 21/1/2013
What I have tried is to extract the number of rows of the OriginalTable and make a double loop as follows:
FOR i=1 TO $(vnRows)
LET vDateStart= num(Peek('Date_start',$(i)-1,'OriginalTable'));
LET vDateEnd = num(Peek('Date_end',$(i)-1,'OriginalTable'));
LET vLoop = $(vDateEnd)-$(vDateStart)+1;
FOR j=1 TO $(vLoop)
Peek('Employee',$(i)-1,'OriginalTable') AS Employee_final,
Date($(vDateStart) + $(j) - 1) as Date_Final
FROM ??????FROM WHERE? THIS IS THE PART I'M MISSING. I TRIED CREATING AN AUXILIAR TABLE BUT GOT SOME ERRORS
Thanks for your help!
I finally solved it, thanks to Bill that gave me the tip (Remove the second loop and use AutoGenerate). It should look as follows:
Date($(vDateStart) + Iterno() - 1) as Date_Final
AutoGenerate 1 WHILE Iterno() <= $(vLoop);
Hope it helps for someone else!
I tried to edit my reply earlier and hit the wrong button & deleted it. Hence why it is no longer here.