Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
happyjoshua777
Creator
Creator

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

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
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
Creator
Creator
Author

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
Partner
Partner

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
Partner
Partner

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
Master
Master

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

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