questions with regards to code containing AUTOGENERATE (1) on p. 296 of QlikView 11 for Devs

[happyjoshua777]

Hello,

Just wanted to clarify a number of points relating to the code snippet below.

1) why is it necessary to subtract '1' from IterNo()? does not IterNo() begin at row number '0' by default?

2) is Temp_Date a placeholder\a pointer to the (nameless) table column which is  populated by the WHILE loop?

3) does WHILE loop extend its scope to the previous two lines, namely: 'MonthStart(...' and 'AUTOGENERATE()'?

4) how does AUTOGENERATE() function know to create a record with the correct format (Date format in our case)? does this function examine the line before in our code and presume that this is the kind of a record it ought to create?

Thank you!

//===========================================================

[Master Calendar]:
LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date, 'YYYY-MM') as [Year - Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;
LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
//--- Remove the temporary variables
LET vMinDate = Null();
LET vMaxDate = Null();

[its_anandrjs]

For point wise

1. IF you not increase num from Iterno() you get one extra row try to remove -1 and check result

2. Temp_Date is the base variable from where to be date start.

3. No while is used to run the loop until its condition fulfil.

4. Autogenerate() is used to create at least one row on table and then rest will carry forward by the Loops in yours script this one WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate) it runs from Min date to Maxdate that is provided on the script.

[happyjoshua777]

with regards to your response to point # 4

the WHILE loop just contains an iterator (IterNo() -1) and the maximum limit (vMaxDate),

where is the action to populate rows with, then?

ta

[Peter_Cammaert]

This

MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

populates a single field in the new table, which is then used in a Preceding Load to stuff the others.

[Peter_Cammaert]

One value at a time (AUTOGENERATE 1)

[its_anandrjs]

Let assume

Let vMinDate = NUM( YearStart( Today() ) ) = 01/01/2017

Let vMaxDate = NUM( Today() ) = 14/09/2017

So while condition checks Min date and Max Date and runs loop until it find max date as num date in five figure

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

[effinty2112 · Accepted solution]

Hi Joshua,

1) IterNo() starts with 1. It is a counter created by the While statement. Don't confuse it with row numbers such as RecNo() or RowNo().

2)Temp_Date is a date representing the start of a month.

3)The While statement controls the load statement above it, in this case

LOAD DISTINCT

MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

AUTOGENERATE (1)

This load statement will continue to be executed (causing an automatic concatenation) until the statement $(vMinDate) + IterNo() - 1 <= $(vMaxDate) is no longer true. Remember that  the value of IterNo() increments after each execution of the load statement.

4) The formatting to the various date formats occurs in the top half of the preceding load after the result of the lower load statement has been passed to it.

Hope this helps.

Regards

Andrew

[its_anandrjs]

You can read some of this for Iterno() and master calendar which can help you to understand.

Iterno() working with while

Creating A Master Calendar

Canonical Date