topic Re: Generate data in a range in QlikView

Generate data in a range

Tue, 12 Nov 2013 15:22:13 GMT

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

Thanks for your help!

Tue, 12 Nov 2013 16:03:06 GMT

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);

Hope it helps for someone else!

Anonymous — Tue, 12 Nov 2013 16:16:49 GMT

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