Preceding Loads in Qlik Sense

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about preceding loads:

If we have to take value judgement on this question based on the amount of likes versus answers, we could maybe say that a preceding load is something people are very interested in, but not much is known about? In any case:

The correct answer is B.

This question actually consist of two parts. We have encountered a preceding load, but also the possibility to use either RowNo() or RecNo(). Lets dive in and find out why answer B is correct in this case.

Preceding Loads

With a preceding load it is possible to pass the previously loaded fields to a new load and do transformations on them while still loading this to the same output table. Please refer to the figure below:

Here we can see that the Sales output table is loaded from Sales.qvd. By adding a new load statement on top of this one, we can pass fields from that first, initial load to a new load. By using the asterisk (*) we determine to carry over all fields, however it is also possible to make selections and only load specific fields. The second load is a new load on those fields, making it possible to do new transformations on those fields, illustrated by creating the measure #PriceExclDiscount, using SalesPrice and DiscountAmount. Remember this order, since this is an important part of answering the question.

Preceding loads can be useful if you need to do multiple calculations or aggregations on the dataset. It will make for clearer and easier to understand script. The example above is quite small, but in some cases when many different prices have to be calculated from a single origin, it is often best to first do the basic calculations in the first load and then load all the measures in the second load statement.

RowNo() vs RecNo()

The RowNo() and RecNo() functions appear similar in function, assigning an integer to the row being loaded, however they have a distinct difference. Where RecNo() looks at the input table and assigns an integer to each row being loaded, RowNo() will look at the output table when assigning numbers to each row.

Answering the question

With our newfound understanding of preceding loads and RowNo() and RecNo() let’s head back to the question. Answer C can be scrapped immediately since removing the alias will not solve anything.

For the other answers the load order comes into play. The bottom part of the load statement is loaded first after which the second load will follow. We have just learned that RowNo() will only evaluate the output table. Since the bottom (first) part of the preceding load is not the output table, RowNo() will not evaluate this and return 0. This means that removing the where exists clause is also not the answer, so D is out of the question.

Then we are left with answers A or B. Changing RowNo() to RecNo() in the question will work, since RecNo() looks at the input table and assigns records to each row of the input table. However, we are looking for RowIDs and not RecordIDs. The difference? Since RecNo() looks at the input table, it doesn’t take the where exists clause in account, whereas RowNo() by looking at the output table does. To illustrate this let’s imagine that we have three ProductID’s previously loaded: A, B and D. And we load the following table using the where exists(ProductID) clause:

Products C and E will be left out of the output table. However since RecNo() has started on the input table, the assigned numbers will differ from the RowNo() function. Therefor this is unusable as a RowID. Leaving us with answer B: move the RowNo() function from the bottom load to the top load.

Some other things to remember

• You can stack as many preceding loads as you wish
• You can choose which fields to pass to the next load. Wildcard (*) being most commonly used to include all
• The granularity for each load can be different
• Wildcard filenames don’t work. You have to be specific about which file to load

That’s it for this week!

More from the Bitmetric team

Qlik Cloud Backup

Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.