topic Re: How does QlikView execute the following Load code? in QlikView

How does QlikView execute the following Load code?

Fri, 29 Jan 2016 17:48:12 GMT

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:

A new table called "ALL_YEARS" with a column called "FYear" is created.
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).
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.
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!

Re: How does QlikView execute the following Load code?

swuehl — Fri, 29 Jan 2016 18:14:38 GMT

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

Re: How does QlikView execute the following Load code?

marcus_sommer — Fri, 29 Jan 2016 19:46:15 GMT

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

Re: How does QlikView execute the following Load code?

Mon, 01 Feb 2016 16:35:10 GMT

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!

Re: How does QlikView execute the following Load code?

swuehl — Mon, 01 Feb 2016 16:58:44 GMT

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;

Re: How does QlikView execute the following Load code?

marcus_sommer — Mon, 01 Feb 2016 16:59:01 GMT

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

- Marcus