Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
happyjoshua777
Contributor

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

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
effinty2112
Honored Contributor

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

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


View solution in original post

7 Replies
Highlighted

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

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.

Highlighted
happyjoshua777
Contributor

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

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

Highlighted

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

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.

Highlighted

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

One value at a time (AUTOGENERATE 1)

Highlighted

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

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

Highlighted
effinty2112
Honored Contributor

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

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


View solution in original post

Highlighted

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

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