Preceding Load

[hic]

A QlikView feature that is poorly known and brilliant in its simplicity is the Preceding Load.

If you don’t know what it is, then I strongly suggest that you read this blog post and find out. Because it will help you in your QlikView scripting.

So what is it?

It is a way for you to define successive transformations and filters so that you can load a table in one pass but still have several transformation steps. Basically it is a Load statement that loads from the Load/SELECT statement below.

Example: you have a database where your dates are stored as strings and you want to use the QlikView date functions to interpret the strings. But the QlikView date functions are not available in the SELECT statement. The solution is to put a Load statement in front of the SELECT statement: (Note the absence of “From” or “Resident”.)

Load Date#(OrderDate,’YYYYMMDD’) as OrderDate;
SQL SELECT OrderDate FROM … ;

What happens then is that the SELECT statement is evaluated first, and the result is piped into the Load statement that does the date interpretation. The fact that the SELECT statement is evaluated before the Load, is at first glance confusing, but it is not so strange. If you read a Preceding Load as

     Load From ( Select From ( DB_TABLE ) )

then it becomes clearer. Compare it with nested functions: How would you evaluate “Round( Exp( x ) )”. You would of course evaluate the Exp() function first and then the Round() function. That is, you evaluate it from right to left.

The reason is that the Exp() function is closest to the source data, and therefore should be evaluated first. It’s the same with the Preceding Load: The SELECT is closest to the source data and should therefore be evaluated first. In both cases, you can look at it as a transformation that has an input and an output and to do it correctly, you need to start with the part of the transformation closest to the input.

Any number of Loads can be “nested” this way. QlikView will start from the bottom and pipe record by record to the closest preceding Load, then to the next, etc. And it is almost always faster than running a second pass through the same table.

With preceding Load, you don’t need to have the same calculation in several places. For instance, instead of writing

Load  ... ,
   Age( FromDate + IterNo() – 1, BirthDate ) as Age,
   Date( FromDate + IterNo() – 1 ) as ReferenceDate
   Resident Policies
      While IterNo() <= ToDate - FromDate + 1 ;

where the same calculation is made for both Age and ReferenceDate, I would in real life define my ReferenceDate only once and then use it in the Age function in a Preceding Load:

Load  ..., ReferenceDate,
   Age( ReferenceDate, BirthDate ) as Age;
Load  *,
   Date( FromDate + IterNo() – 1 ) as ReferenceDate
   Resident Policies
      While IterNo() <= ToDate - FromDate + 1 ;

The Preceding Load has no disadvantages. Use it. You’ll love it.

HIC

[hic]

I would put any transformation or string operation or creation of a new field in a preceding Load, rather than in a resident Load. I.e. if possible - always choose preceding Load.

But there are some transformations you cannot do in a preceding Load, e.g anything that you want to do after a Crosstable Load or after a Join. For these I would use a resident Load.

HIC

[#3]

Hello Henric,

Thanks for the reply.

I hope Like Resident Load,Preceding Load also takes data from RAM instead of the datasource directly(qvd etc...).

If i'm correct why does it take lesser time than the Resident Load ?

Regards

[hic]

Let's say that you have the following construction:

          Load *, SomeFunction(X,Y) as Z; // Load statement "B"

          Load X, Y From ... ;            // Load statement "A"

Then each record that is loaded by "A" is piped into "B". This means that "B" processes the first record completely before "A" starts to process the second record. In other words: the result of "A" is never stored as a table - neither on disk, nor in RAM. So you can't say that "B" loads a table from RAM or from disk.

The alternative to the above constuction, is to use a resident Load:

          A: Load X, Y From ... ;            // Load statement "A"

          C: Load *, SomeFunction(X,Y) as Z Resident A; // Load statement "C"

          Drop Table A;

If the "A" statement alone takes 10 s, then A+B typically would take perhaps 11-12 s and "C" would perhaps take 9-10 s. So the above construction would all in all take 11-12 s and the lower construction would take 19-20 s.

Does this make sense?

HIC

[#5]

I understand now.

Thanks a lot Henric for the explanation

Regards

[#6]

Hi,

It also does not work with crosstable. Anyone got a solution for that?

Kind Regards,

Dion

[hic]

The preceding Load cannot be used after (above) a Join or a Crosstable. The work-around is to do it in two steps:

// === Step 1 ===

TempTable:

Crosstable Load ... From ...;

// === Step 2 ===

FinalTable:

NoConcatenate

Load ... Resident TempTable;

Drop Table TempTable;

HIC

[#8]

Thx, I find it hard to fully understand, perhaps because I don't even know the alternative/traditional way - non-preceding load. Can anyone suggest an alternative explanation? 

[hic]

http://www.learnallbi.com/preceding-load-in-qlikview/

http://qlikviewnotes.blogspot.se/2009/12/simplify-with-preceeding-load.html

http://www.qliktips.com/2012/12/powerful-preceding.html

[#10]

thx I have it

[userid128223]

can you explain why i get error in this code and how preceding load will help. i get field not found error.

empData:

LOAD ID,

     Name,

     Amount,

LOADID & | & Name as uniqkey

FROM QVD.qvd (qvd);