topic Re: Qlikview 11 for developers - Understanding while loop and master calendar in QlikView

Qlikview 11 for developers - Understanding while loop and master calendar

Wed, 23 Apr 2014 22:01:27 GMT

This is from page 295. I don't understand how the looping works to generate temp_date. I know what is happening in the end - I get a calendar with all dates between two ranges. But, I don't understand how the steps are executed logically by Qlikview. Can someone please explain the logic in the while load, step by step ?

[Master Calendar]:

LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period]....etc.
;

LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

If this was a regular programming language, I'd do it like this -

int vMinDate = 1234;

int vMaxDate = 6789;

while(vMinDate < vMaxDate)

{

Add a row which is equal to vMinDate in a Field;

vMinDate = vMinDate + 1;

}

Re: Qlikview 11 for developers - Understanding while loop and master calendar

swuehl — Wed, 23 Apr 2014 22:12:06 GMT

The While loop is generating 1 record as long as the condition is true.

The important thing to know is that Iterno() returns the current iteration, i.e. 1 for the first execution, 2 for the second etc.

Re: Qlikview 11 for developers - Understanding while loop and master calendar

Wed, 23 Apr 2014 22:16:41 GMT

Thanks. Can you also tell me how qlikview executes it, step by step ? The programming code which I gave is so simple and self-explanatory. But, the qlikview script is not.

Re: Qlikview 11 for developers - Understanding while loop and master calendar

swuehl — Wed, 23 Apr 2014 22:39:58 GMT

The programming code is probably simple and self-explanatory because you know the syntax.

To understand the QV script, you need to know some of the QV basics, e.g. input table / output table of a LOAD statement. Do you know the difference?

Maybe it's easier if you are trying to describe how you interprete the code, instead me more or less repeating something that's probably already written in the book.

Re: Qlikview 11 for developers - Understanding while loop and master calendar

Wed, 23 Apr 2014 22:50:38 GMT

I read the manual and figured it out. Only one minor question remains. Why generate from month start instead of actual min date ?

LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Preparation -

IterNo() = 0, since we have not started iterating yet.

Let the date for current iteration be currDate.

currDate = $(vMinDate) + IterNo() - 1 = 1 + 0 - 1 = 0

vMinDate = 4/24/2014 (say. day 1)

vMaxDate = 4/30/2014 (say. day 7)

Steps -

1. Create column Temp_Date in memory.

2. IterNo() = IterNo() + 1

3. currDate = 1 + 1 - 1 = 1 (I.e day 1)

4. add currDate as row to Temp_Date using AUTOGENERATE (1).

5. Repeat steps 2 to 4 while currDate <= vMaxDate

Re: Qlikview 11 for developers - Understanding while loop and master calendar

swuehl — Wed, 23 Apr 2014 23:04:42 GMT

That's a good question, I would also create the master calendar a little different. But without reading the part of the book, I don't know what the author intended. I agree it looks a bit complicated and creates more records first then needed at the end.