Filtering data from a QVD file takes too much time to reload

[VijaySekar]

I have a qvd file which is combination of year wise data. I tried to filter this qvd file by using where condition in the load script like

WHERE YEAR(date_column) = '2022'

But it takes too much time to reload. As qvd files are in memory filtering on qvd file should be faster right? 

Its so frustrating that it takes too much time to load from a qvd file

Thanks

[BrunPierre]

Hi, @VijaySekar You can generate a table that will automatically create a range of dates for you.
Then, you can load your QVD using the 'Where EXISTS' clause, resulting in an optimized load that conserves server resources and significantly reduces the reload time.

LET vDateMin = YearStart(AddYears(Today(), -1));
LET vDateMax = YearEnd(AddYears(Today(), -1));

TmpDate:
LOAD Date(Date('$(vDateMin)')+IterNo()-1) as Transaction_Date
 AutoGenerate(1)
 While Date(Date('$(vDateMin)')+IterNo()-1) <= Date('$(vDateMax)'); 

Transactions:
LOAD *
FROM Transactions.qvd (qvd) 
Where Exists Transaction_Date;
Drop Table TmpDate;

[tsbrian]

Hi @BrunPierre 

Is the Transaction_Date column in TmpDate table is the column that already exists in Transactions QVD file?

If i use the same code i am getting column not found error. 

My qvd file has "transaction_created_date" column but in the TmpDate table we are creating Transaction_Date as new column. Using this column in where clause throws column not found error.

[marcus_sommer]

The field-names from the temp-table and the one from the qvd should be the same and it's easy to adjust it appropriate within the temp-load.

[Or]

As explained by @BrunPierre , if you want to benefit from optimized QVD loading, you need to avoid Where statements with the exception of a simple WHERE EXISTS clause. See e.g. https://community.qlik.com/t5/QlikView-App-Dev/What-is-optimised-QVD/td-p/800687 as this issue has been covered extensively both in Community and around the web.