Whilst running a recent training course I was surprised to find some reasonably experienced delegates were not aware of layering multiple Preceding Loads. Time, therefore, for another Back To Basics post.

Introduction

This series of posts looks at some of the features rarely blogged about as they are so second nature to many experienced QlikView bloggers. The sort of features I will use on a daily basis without thinking about it. These techniques are useful to revisit though and for anyone who has not come across these features before they are the things you really should become aware of. Preceding Loads deserve to be added to the list of features covered here.

What Is A Preceding Load?

As the name suggests, the preceding load happens in front of another load. You have probably used one already – even if you were not aware of it. When loading from an ODBC or OLEDB data source you have a SQL SELECT statement and the wizard will (optionally) add a LOAD section ahead of the SELECT. This is a basic preceding load. I would recommend that you always use one of these ahead of a database load – as it opens up a whole range of syntax that is not available in the SQL statement. So, if that is a simple preceding load, what can you do with a more complex one?

Potential Confusion

Before we get stuck into preceding loads fully it is important to understand the order things happen in your load script. In the main, script is executed from top to bottom, then let to right along the tabs (the tabs have no functional relevance are only there to tidy code). Loops and subroutines will alter this execution path, by design, but apart from that this statement holds true. The gotcha however is with LOAD blocks, which can be stacked together and execute from the bottom up. Picture the simple SQL preceding load:

Preceding load on a database call

The SQL part is executed and returned first and is then parsed by the LOAD statement above. This idea of loading from the bottom up holds true as we get into multiple layers of preceding loads, and it is important to keep in mind as we continue.

Why Use A Preceding Load?

Put simply, a preceding load allows you to use values derived in one part of the load in the one above it. To give a simple example, let’s say we are loading two dates from a text file and we want to know how far apart those days are. We need to convert those dates from the text values to numeric ones we can do arithmetic on and only then can we do the difference calculation. Without a preceding load the code will look like this:

A date interval with no preceding load

You will notice that there is code duplication as we convert both of the dates twice. Whilst I have been very reliably informed (by Henric Cronström) that QlikView with it’s clever caching will not need to calculate the values twice, duplication is a bad thing from a code maintainability point of view.

With a simple preceding load we can remove that duplication and make the code cleaner and more readable. It will then look like this:

A simple preceding load cleans the code

You will note that the fields we created in the lower part of the load are then used in the one above.

Also of note here is the asterisk in the preceding load. This is required to pull the fields up from the load below up to what is actually loaded. There are two potential problems you may face here. First if you omit the * then the two date fields will not be in the final data model. The other potential problem is that you can duplicate a field by having a field name used in the succeeding load that is pulled through with a * that is then used again in the preceding load. These can be hard to spot and the error message from QlikView (field names must be unique) does not always point you to the right part of the load script – so be careful.

Generally I would advise against the use of an asterisk (particularly when pulling fields from a database) but in preceding loads they are most useful. Be aware you can also pull fields up explicitly by listing them if you want to take only some fields from your lower LOAD to the preceding one.

Onwards And Upwards

And you don’t need to stop there. If you wanted to have another value calculated on fields derived in your preceding load you can add a preceding load on your preceding load. To take the example of our date interval we could then add another field based on whether a threshold has been breached or not.

Simply stack another load on top of the one before, like this:

Preceding layers can be stacked on top of each other

There is no limit to the number of levels of load you can have. And whilst it may sound like you are creating a potential confusion, like a BI version of Inception, preceding loads tend to help you to clean and simplify script. You can have expressions that use fields from any of the levels below to create new values.

Advanced Features

Whilst it doesn’t really belong in a back to basics post, I should mention that there are functions you can use in your preceding loads that you may previously have only associated with the first level of load. WHERE, WHILE and GROUP BY are all permissible using values from the load before. You should probably not use these features often, but it is with knowing they are there.

Conclusion

Having preceding loads in your kit bag of QlikView load script code allows you to build complex expressions, whilst keeping your code simple by breaking things up into bite sized chunks. Duplication can be removed and maintainability can be improved. Not bad for a simple little technique.

This is the sixth post in the Back To Basics series. Look out for the next post in the series soon. If you have a suggestion on a topic please let me know in the comments field below.