Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Generate data in a range

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

Eg:

OriginalTable:

Nick  1/1/2013       2/1/2013   2
John  9/1/2013     11/1/2013   3
Nick  19/1/2013   21/1/2013   3


I want to create a new table containing all the dates of the corresponding range:


FinalTable:

Nick 1/1/2013
Nick 2/1/2013
John 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)

        

       

        FinalTable:

        LOAD

        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

    NEXT

NEXT

Thanks for your help!

2 Replies
Not applicable

Re: Generate data in a range

I finally solved it, thanks to Bill that gave me the tip (Remove the second loop and use AutoGenerate). It should look 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;

   

        FinalTable:

        LOAD

        Peek('Employee',$(i)-1,'OriginalTable') AS Employee_final,

        Date($(vDateStart) + Iterno() - 1) as Date_Final

        AutoGenerate 1 WHILE Iterno() <= $(vLoop);

NEXT

Hope it helps for someone else!

Re: Generate data in a range

Carlos

Ooops!!

I tried to edit my reply earlier and hit the wrong button & deleted it.  Hence why it is no longer here.

Silly Billy

Community Browser