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

[#2]

Thanks for the good post, really worth to read .

[clisboa_noesis]

Hi Henric,

I use Preceding Load very often, but there's only one disadvantage that i know off.

If you're loading several files with a wildcard, the preceding load will not only be processed for the first file, but it will also break the 'auto' concatenation.

Check the following demonstration: http://dl.dropbox.com/u/3653801/preceding%20load.zip

Best Regards,

Carlos

[hic]

You're absolutely right.

I didn't think of this complication since I see it as a flaw of the wildcard load - which I stopped using years ago. Instead I use a For-Next loop:

For each vFile in FileList('C:\path\*.txt')

          Load ... From [$(vFile)] ;

Next vFile

Which is identical to

Load ... From ;

And within the For-Next loop you can put as many Preceding Loads as you wish...

HIC

[Gabriel]

Hi HIC,

Another great QV tips.

[stevedark]

As well as the wildcard gotcha, doing a preceding load on a crosstable can also not behave as you would expect.  This is one of the cases where I would load into a temporary table and then do further work on a RESIDENT load.

Thanks for another useful post Henric!

[#7]

Thanks for another wonderful post Henric!

Preceding is definitely quite  handy to make script easy to read and maintain but one must remember to balance it coz overdoing its nesting can sometimes cause reloads slow as a preceding load does has some overhead and writing the expression multi times can result in a faster load depending on the expresisons used in load script.

Just my 2 cents!

Regards,
Ashutosh

[#8]

Hi Henric,

Could you please also throw some light on the comparison between Resident Load & Preceding Load ?

Is it always better to use Preceding Load instead of resident load to add few more calculated cols using the already loaded fields ?

Is it faster than Resident Load ? etc...

Thanks for your time

Regards

[Anonymous]

Thanks HIC. I have used this for doing a small ETL work.

I dont see any performance drawback. But it all depends on what kind of expression is written over there.

HIC, i wish to hear from you +ve and -ve of Resident load vs Preceding load. Can you let us know from your experience ?

[hic]

If you compare a single preceding Load with a single resident Load where the two Loads have identical content, then I would say that the preceding Load always is faster. In the preceding Load, the records are evaluated "on the fly" and this makes it fast.

I made a small test where I had a Load with a few string operations. When I put them in a preceding Load, the script execution time increased by 15-20%. When I put them in a resident Load, script execution time increased by 90-100%. So, I would say: If you can put your calculation in a preceding Load, you should. But sometimes you have to use a resident Load, e.g. after a Crosstable or when you want to make some transformations after a Join.

However, a preceding Load has a small overhead, so if you have multiple preceding Loads, it will be slower. But if a preceding Load helps the manageability and clarity of the script, then I am usually willing to take a small penalty.

HIC

[Anonymous]

Thanks a lot HIC. So according to you, transformations and string operations and for cross tables, i can go for resident. Calculations and small operations i can go for Preceding.