I have the following script for a QVD I need to create. Basically, I need to create this QVD to include all shipped orders, which was answered in a previous post, so I have that answer. But I need to now reference an external spreadsheet with just a list of customer numbers (SAP_CUST_NO) to exclude from the QVD creation as I do not want to see them in this data set. How do I do that in the script?
Re: Re: QVD Creation with inclusions and exclusions?
Where exists would do, but with a bit of convolution.
Load Distinct SAP_CUST_NO From ListExcel;
Noconcatenate Load Distinct SAP_CUST_NO From SourceExcel
Where Not Exists (SAP_CUST_NO);
Drop Table tmp;
Load [... fields...] From SourceExcel Where Exists(SAP_CUST_NO);
Drop Table tmp2;
Store SHIPMENTS_AS_DEPLETIONS_SHIPPED into (whatever you want);
Drop Table SHIPMENTS_AS_DEPLETIONS_SHIPPED;
You might wonder why not use Not Exists(...) more directly - that's because it would eliminate all records but the first with duplicate SAP_CUST_NOs from the loaded data (the list of existing values gets updated after each record loads, not after the whole table finishes loading).
FROM (ooxml, embeddedlabels, tableis Data) WhereMatch (ORD_STATUS, 'S') And Not Exists (SAP_CUST_NO);
STORE SHIPMENTS_AS_DEPLETIONS_SHIPPED INTO C:\Users\greenejo\Desktop\Qlikview Development Projects\Depletions Project\QVDs\SHIPMENTS_AS_DEPLETIONS_SHIPPED.qvd (qvd); Droptable SHIPMENTS_AS_DEPLETIONS_SHIPPED;