Qlik - Optimized Load with where clause and date

Very often it is necessary to load partially a huge QVD which contains historical data since when you were even alive.

Jokes aside, there is a fantastic way of doing that without writing a lot of code and having an Optimized Load.

Say you have a historical QVD from 2017, which contains 500 million records. So, instead of using where date_field >= ‘2017–05–01’, you can create a table which will autogenerate the range of dates for you and then you can load you QVD suing Where EXISTS and voila, you will have an optimized load saving server resources and reducing considerably the reload time.

LET vDateMin = AddMonths(Today())-60; //5 Years from now
LET vDateMax = Today();
TMP_DATE_TABLE:
LOAD
 Date(Date(‘$(vDateMin)’)+IterNo()-1) as Sales_Date
 AutoGenerate(1)
 While Date(Date(‘$(vDateMin)’)+IterNo()-1) <= Date(‘$(vDateMax)’); 
 
Sales:
LOAD 
 Sales_ID,
 Product_ID
 Price,
 UnitPrice,
 Discount
 Sales_Date
FROM Sales.qvd (qvd) 
WHERE EXISTS Sales_Date;
Drop Table TMP_DATE_TABLE;

In case you don't know what is the maxdate of your QVD there’s an easier way to find out:

//Optimized load
TMP_INV_DATE: 
Load INV_DATE 
FROM invoice_data.qvd (qvd);
TMP_MAXINV_DATE:
//preceding load
Load Max(INV_DATE) as maxinvdate;
;
Load FieldValue(‘INV_DATE’,IterNo()) as INV_DATE
AutoGenerate(1)
While not Isnull(FieldValue(‘INV_DATE’,IterNo()));
Let var_maxinvdate = Peek(‘maxinvdate’,-1,’TMP_MAXINV_DATE’);
Drop tables TMP_INV_DATE, TMP_MAXINV_DATE; //not needed

I hope you guys enjoy these nice tricks! Stay tuned for upcoming articles.