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

Mon, 03 Aug 2015 18:45:58 GMT

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);

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

maxgro — Mon, 03 Aug 2015 19:17:01 GMT

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;

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

Tue, 04 Aug 2015 15:36:20 GMT

Thanks Massimo, this was really helpful.

topic Re: How to optimize a load qvd statement with Where field < value in QlikView

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

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

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