Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to optimize a load qvd statement with Where field < value

So I'm looking for ways to optimize the following load statement, and everything I have seen is that you can only use WHERE statements if it is WHERE EXISTS.  Currently we only want to load the past 24 months of data, so we have created a cutoff variable, which is then referenced in our where clause on the load statement.  The QVD that we are working with is 26 gigs and currently to run the current load statement takes 1.5 hrs.  I'm looking for ways to speed this up, but haven't been able to wrap my brain around how I would change the date from WHERE < value to WHERE exists.  Here is the load statement and just looking to see if anyone had any suggestions.

Thanks

Jeremy Kofoot

Let vToday  = today();
Let cutoff = vToday - 729;


EBILL:   
LOAD [Tracking Number EBill],
TNumYear as TrackNumKey,
[Charge Description Code txt],
[Account Number],
[Invoice Date],
[Invoice Number],
[Invoice Amount],
[Transaction Date],
[Package Quantity],
[Package Reference Number 2],
[Package Reference Number 4],
[Entered Weight],
[Billed Weight],
[Package Dimensions],
Zone,
[Charge Category Code],
[Charge Category Detail Code],
[Charge Source],
[Type Code 1],
[Type Detail Code 1],
[Charge Classification Code],
[Charge Description Code],
[Charge Description],
[Net Amount],
[Sender Name],
[Sender Company Name],
[Sender Address Line 1],
[Sender Address Line 2],
[Sender City],
[Sender State],
[Sender Postal],
[Sender Country],
[Receiver Name],
[Receiver Company Name],
[Receiver Address Line 1],
[Receiver Address Line 2],
[Receiver City],
[Receiver State],
[Receiver Postal],
[Receiver Country],
[Third Party Name],
[Third Party Company Name],
[Third Party Address Line 1],
[Third Party Address Line 2],
[Third Party City],
[Third Party State],
[Third Party Postal],
[Third Party Country],
[Miscellaneous Line 1],
[Miscellaneous Line 2],
[UPS Shipment Quantity],
[UPS Accessorial Charges Billed],
[UPS Freight Cost Billed],
[UPS Fuel Surcharge Billed]
FROM \\...\shipping.qvd (qvd)
WHERE [Transaction Date] > $(cutoff);

1 Solution

Accepted Solutions
maxgro
MVP
MVP

you can try with something like this for an optimized load

// make a temp table with the date you want to load

tmp:

load

  date(today() - 729 + rowno()) as [Transaction Date]

AutoGenerate 729;

// load with exists (optimized)

load

  *

FROM \\...\shipping.qvd (qvd)

WHERE exists([Transaction Date]);

drop table tmp;

View solution in original post

2 Replies
maxgro
MVP
MVP

you can try with something like this for an optimized load

// make a temp table with the date you want to load

tmp:

load

  date(today() - 729 + rowno()) as [Transaction Date]

AutoGenerate 729;

// load with exists (optimized)

load

  *

FROM \\...\shipping.qvd (qvd)

WHERE exists([Transaction Date]);

drop table tmp;

Not applicable
Author

Thanks Massimo, this was really helpful.