Preceding Loads

[EMaebe]

Good morning! 

I'm building an app which uses preceding loads to reference created fields in the initial load statement. However, I'm wondering if there's an impact on load time using preceding loads? What's the best practice for using preceding loads?

In the app I'm currently working on, I have 2 preceding loads. One builds a "Cust_Ord_Type" that is then used in the next preceding loads to create a "Cust_Ord_Status" (past due, current, etc) based on dates I've created using nested ifs compared to today (so they change) in the original load statement. I have another, unrelated formula that I need to create in a preceding load as it references a field in the original load. Should I use the first preceding load with the cust_ord_type nested if statement or should I create a 3rd preceding load statement for this new field I'm going to create?  Does having multiple preceding loads impact load times? 

The load time is currently approx. 1 min and I want to be able to refresh every other hour, so I'd like to keep the load time very short. 

[vinieme12]

1min is really short; I'm assuming the dataset is very small ,

You may try resident load but the the difference if any could be negligible 

here is a good read

https://qlikviewcookbook.com/2018/03/preceding-load-performance-update/

[marcus_sommer]

From a theoretically point of view a preceding-load should be (always) faster as n resident loads. Like very well described from Rob within the provided link it was not always the case.

Beside this and a direct comparing of both methods with your data - which makes usually not much efforts - you should develop everything with preceding-loads because of the easiness and readability benefits. If there is really a critical run-time you could yet checking if a resident-load is faster and/or if other optimizations are possible (the last seems very likely for me because you mentioned nested if-loops which are never fast and might be replaceable by mappings or other methods).

[EMaebe]

Right now the dataset is around 550k rows, but I fully expect it to grow exponentially as we will be keeping all historical data. I have a data warehouse pulling from a view from a WMS (transactional data). I also need to keep the load very short for all of my apps due to needing to reload on an hourly or bi-hourly basis. 

[EMaebe]

Thank you for your response. I'm still learning the best way to optimize my apps. Could you suggest any readings for using mappings vs nested ifs? I'm pretty sure I've done a mapping once vs a nested if, but it's been a while so I'll look through my prior apps that I've created and my notes.  I'm thinking I could use that for the Cust_Ord_Type (below is the nested if statement I'm using). PA?, Customer Channel, IS RDC?, and IS CAD? are created in the original load. 

IF(
[PA?]='pa',
'PA',
IF(
trim([PA?])<>'pa'
AND [Customer Channel]='OE'
AND [Is RDC?] = 'IRDC'
, 'RDC'
, IF(
trim([PA?])<>'pa'
AND [Customer Channel]='OE'
AND [Is RDC?] = 'NRDC'
, 'OE'
,IF(
trim([PA?])<>'pa'
AND [Customer Channel]='DL'
AND [Is CAD?] = 'NCAD'
, 'Dlr'
, IF(
trim([PA?])<>'pa'
AND [Customer Channel]='PR'
AND [Is CAD?] = 'NCAD'
, 'PRI'
, IF(
trim([PA?])<>'pa'
AND [Customer Channel]='WD'
AND [Is CAD?] = 'NCAD'
, 'ABC'
, IF(
trim([PA?])<>'pa'
AND [Customer Channel]='UD'
AND [Is CAD?] = 'NCAD'
, 'USD'
, IF(
trim([PA?])<>'pa'
AND [Customer Channel]='UD'
AND [Is CAD?] = 'ICAD'
, 'CAD'
,''
)
)
)))))) as "Cust_Ord_Line_Type"

I'm not sure whether I could use those for the dates I'm creating which are cutoff dates for what's considered "Current cutoff", "past due cutoff", and "Tomorrow Starts". I have 11 different types of dates that I'm creating using nested ifs with weekday(), today() and/or FirstWorkDate() to exclude weekends & holidays that we don't work. 

Here are a couple examples of the dates I'm creating: 

,Date(Today()-WeekDay(Today())-3, 'YYYY-MM-DD hh:mm:ss') as "XYZ_PD_Cutoff"

,If(weekday(today())=5,
Date((today()+2)+15/24,'MM-DD-YYYY hh:mm:ss'),
If(weekday(today())=6,
Date((today()+1)+15/24,'MM-DD-YYYY hh:mm:ss'),
Date(today()+15/24,'MM-DD-YYYY hh:mm:ss'))) as "ABC/D_Current_Cutoff"

,If(weekday(today())=5,
Date((today()+2)+901/1440,'MM-DD-YYYY hh:mm:ss'),
If(weekday(today())=6,
Date((today()+1)+901/1440,'MM-DD-YYYY hh:mm:ss'),
Date(today()+901/1440,'MM-DD-YYYY hh:mm:ss'))) as "ABC/D_Tmrw_Start"

I then have a nested if statement that uses those dates I've created and the cust_ord_line_type to identify if an order (each row represents an order) is past due, current, or tomorrow.

[marcus_sommer]

That your dataset might be growing very large is quite irrelevant to the question if preceding- or-resident-loads have a better performance. If the dataset is too big - not only from the processing point of view in Qlik else also how fast could the database and the network transfer the data and disregarding the aspect to repeat such a workload regular with redundant data - you need to consider to apply any incremental approach by loading + processing only new/changed data and the historic ones are added from an optimized qvd-load.

[marcus_sommer]

Yes, these logic could be optimized. The categorizing of the customer-type might be replaced by a mapping which combines your 3 fields as lookup. Creating such mapping-table in Excel isn't really difficult and even by a larger combination of values the most work could be done with copy & paste jobs within appropriate filters.

Mapping | QlikView Help

Also the various date/time-matching could be done per mapping against a master calendar/timetable respectively a special matching of them.

[QFabian]

Hi @EMaebe @marcus_sommer @vinieme12 , I found this article for more documentation.

https://community.qlik.com/t5/Design/Preceding-Load/ba-p/1469534

From a visual/process perspective, you can  use Resident Loads to make steps of partial calculations just following the natural Load Script execution .