topic Re: Filtering data from a QVD file takes too much time to reload in App Development

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

VijaySekar — Tue, 25 Jul 2023 16:56:08 GMT

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

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

BrunPierre — Tue, 25 Jul 2023 23:16:03 GMT

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;

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

tsbrian — Wed, 26 Jul 2023 07:21:58 GMT

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.

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

marcus_sommer — Wed, 26 Jul 2023 10:25:39 GMT

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.

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

Or — Wed, 26 Jul 2023 10:48:36 GMT

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.