Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Assuming a table is already loaded into QlikView with a column "Year" with values {2010,2011,2012,2013,2014,2015}, I enter this code:
ALL_YEARS:
Load
Max(FYear) as MaxYear,
Min(FYear) as MinYear;
Load
FieldValue('Year',IterNo()) as FYear
AutoGenerate(1)
While not Isnull(FieldValue('Year',IterNo()));
Let vStartYear = Peek ('MinYear');
Let vEndYear = Peek ('MaxYear');
My understanding is thus:
Please let me know if my understanding is flawed. Please also let me know if this code can be more efficient. Thanks!
Have a look at
If you want to put the aggregations after the FYear creation, you would need to use RESIDENT LOAD:
TMP:
Load
FieldValue('Year',IterNo()) as FYear
AutoGenerate(1)
While not Isnull(FieldValue('Year',IterNo()));
ALL_YEARS:
Load
Max(FYear) as MaxYear,
Min(FYear) as MinYear
RESIDENT TMP;
DROP TABLE TMP;
3. The Max() / Min() functions will aggregate FYear values, resulting in a single row with the min resp max FYear value.
Also, the FYear field itself is dismissed in the preceding LOAD (so there are no 'additional' columns, only two resulting columns).
This goes easier if you used this:
LOAD max(FieldValue(‘Id’, recno()))
AUTOGENERATE FieldValueCount(‘Id’);
from the example here: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook
Essential by this approach is not to run through a table else through the distinct field-values.
- Marcus
Hi, a follow-up beginner's question: what is the benefit of scripting in this order:
ALL_YEARS:
Load
Max(FYear) as MaxYear,
Min(FYear) as MinYear;
Load
FieldValue('Year',IterNo()) as FYear
AutoGenerate(1)
While not Isnull(FieldValue('Year',IterNo()));
1) Why is the "ALL_YEARS" table load script in front of the 2nd load script (with "AutoGenerate") when the 2nd load script will be executed first by QlikView?
2) Would it be wrong if the 2 loads are reversed (IE: the "ALL_YEARS" table load script is placed behind the load script with "AutoGenerate"?
Thanks!
Have a look at
If you want to put the aggregations after the FYear creation, you would need to use RESIDENT LOAD:
TMP:
Load
FieldValue('Year',IterNo()) as FYear
AutoGenerate(1)
While not Isnull(FieldValue('Year',IterNo()));
ALL_YEARS:
Load
Max(FYear) as MaxYear,
Min(FYear) as MinYear
RESIDENT TMP;
DROP TABLE TMP;
This exception from the normal load order (from top to bottom and left to right) is called Preceding Load.
- Marcus