Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How does QlikView execute the following Load code?

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:

  1. A new table called "ALL_YEARS" with a column called "FYear" is created.
  2. The Load-While loop will populate the new table with 6 rows with values {2010,2011,2012,2013,2014,2015} and terminate once no data is found (Not Isnull).
  3. 2 additional columns ("MaxYear" and "MinYear") will be created in the new table "ALL_YEARS" with the value of 2015 for all "MaxYear" rows and 2010 for all "MinYear" rows.
  4. 2 variables ("vStartYear" and "vEndYear") will be created with values of 2010 and 2015, respectively.

Please let me know if my understanding is flawed. Please also let me know if this code can be more efficient. Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Have a look at

Preceding Load

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;

View solution in original post

5 Replies
swuehl
MVP
MVP

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

marcus_sommer

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

Not applicable
Author

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!

swuehl
MVP
MVP

Have a look at

Preceding Load

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;

marcus_sommer

This exception from the normal load order (from top to bottom and left to right) is called Preceding Load.

- Marcus