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

[naziralala]

Thanks. I have the issue resolved.

[#3]

Thank you!

[joydipp1988]

Dear HIC,

1) So in case of Preceding load how the flow of execution happens ? Is it Top to Bottom or Bottom to Top approach ?

2) In general when we load data from external sources it stored into RAM but in case of preceding load data gets loaded from external sources but not stored into RAM. It further processed and in the top most i.e the final stage it occupies RAM. Am I correct sir ?

[joydipp1988]

Dear sir,

I tried applying Order By statement on top of a initial load. But it throws an error. I think Order by isn't possible in Preceding Load. Am I right sir ?

[hic]

The "Preceding Load" is effectively executed from bottom to top.

Data is always stored in RAM. But the point is, that if you have one or several preceding loads, only the output of the top Load is accumulated - it is appended to a table. The other Load statements just process a record and then pass it on to the Load above.

Order By can only be used in a "Load * Resident".

HIC

[joydipp1988]

Thanks for the quick reply sir. Its more clear now.

[beck_bakytbek]

Thanks for sharing

[Anonymous]

Thanks Henric. It was very helpful to understand the 'preceding load'

[Anonymous]

Hello Henric

In one of your above replies you have mentioned that preceding load cannot be used above joins or concatenate. In most of the situations that I have encountered I have used joins or concatenated load statements. Hence I have used resident load everywhere to do the transformations. Will it be possible to share an example or a workaround how preceding load can be used with joins or concatenating load statements.

In any of the blog posts I have not seen an example posted by anybody using concatenated or multiple join load statements using preceding load.

[hic]

The problem arises when you want to perform a join in a first step and a preceding load in a second step, i.e.

   Load ... ; ( Load ... From x; Join Load ... From y; )

This is not possible. You cannot use brackets this way, and if you remove the brackets the preceding load is only applied on the first load (on x). So instead, the work-around is to do it in two steps:

   tmp:

   Load ... From x; Join Load ... From y;

   RealTable:

   Load ... Resident tmp;

   Drop Table tmp;

For concatenate, there is an additional work-around: To repeat the preceding load:

   Load ... ;

   Load ... From x;

   Concatenate

   Load ...;

   Load ... From y;

HIC