Explanation of Code to Find Max QVD Date

[fietzn]

Hi,

I came across multiple posts referencing the method below for finding the max date in a QVD (main source from Rob Wunderlich). I'm able to get this to work in my code. However, I don't understand what rows 2 and 3 are doing and why they're necessary, even after getting a basic understanding of the functions being used.

Is there someone who can explain what is going on in each line?

LOAD Max(Id) as MaxId;
LOAD FieldValue(‘Id’, recno()) as Id
AUTOGENERATE FieldValueCount(‘Id’); 

Thanks.

[henrikalmen]

First this line is executed:

LOAD FieldValue(‘Id’, recno()) as Id AUTOGENERATE FieldValueCount(‘Id’); 

After that, the first line in your example is executed. This way of writing load statements is called a preceding load, and it uses the result from the table created in the first step.

LOAD Max(Id) as MaxId;

The resulting table will be the result of this last execution; a table with a field named MaxId and it has one row.

Does that answer your question?

[fietzn]

Hi @henrikalmen,

Thanks for the quick response. I didn't realize this is executing in reverse of what's displayed, so that's helpful. A couple follow-up questions:

1. How do we know when a preceding load is in effect? Is it any time multiple load statements exist as part of a single table?
2. For this line of code, is it essentially generating a table of all the unique 'Id' values?

LOAD FieldValue(‘Id’, recno()) as Id AUTOGENERATE FieldValueCount(‘Id’); 

[Chanty4u]

Please go through basic concepts and practice some examples.

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/precedi...

[henrikalmen]

1. Preceding load statements are preceding(!) the base load statement, and they are missing a data source of their own (for example there's no FROM statement in the load statement that is preceding the base statement). See the link from @Chanty4u and read all about it.

2. The blog post you are referring to (that your example script is coming from) is discussing what the fastest way to obtain a max-value field is in different situations. If you are fairly new to Qlik Sense, maybe you should choose another approach where you understand what's going on in your script. Especially if your data set is not very large, load time might not be an issue for you. The first example in that blog post - load max(Id) FROM (qvd); - is easier to understand.

[fietzn · Accepted solution]

After speaking with others, I was able to figure this out. Posting commented code to explain what's happening in case others find it helpful in the future. Load time was an issue for me, which is why I was looking for an alternative solution to find the max date.

// This section runs 2nd because it’s a preceding load
// Now we only look through the table of unique ‘Id’ values instead of all values
LOAD Max(Id) as MaxId;
// Generate table of unique values of ‘Id’ instead of all values
LOAD FieldValue(‘Id’, recno()) as Id
AUTOGENERATE FieldValueCount(‘Id’);