Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
LOAD
COMPANY_CODE,
SALES_ORD_NBR,
SALES_ORD_LINE_NBR,
SAP_CUST_NO,
SALES_ORG,
DIST_CHANNEL,
CUST_PO_NBR,
EDI_ORD_FLAG,
SALES_DOC_TYPE_CD,
ORD_REASON_CD,
ORD_STATUS,
PICKUP_FLAG,
CUST_ORD_DATE,
ORD_DATE,
REQST_DEL_DATE,
PLAN_DEL_DATE,
ACT_DEL_DATE,
SAP_ITEM_NO,
UOM_BASE,
UOM_SELL,
UOM_WGHT,
ITEM_CAT_CD,
SHIP_PLANT,
MAD_DATE,
PLAN_SHIP_DATE,
ACT_SHIP_DATE,
ORD_QTY_BASE,
ORD_QTY_SELL,
ORD_GROSS_WGHT,
ORD_NET_WGHT,
SHIP_QTY_SELL,
SHIP_QTY_BASE,
SHIP_GROSS_WGHT,
SHIP_NET_WGHT,
REJECT_CD
FROM
(
Where Match (ORD_STATUS, 'S');
STORE SHIPMENTS_AS_DEPLETIONS_SHIPPED INTO C:\Users\greenejo\Desktop\Qlikview Development Projects\Depletions Project\QVDs\SHIPMENTS_AS_DEPLETIONS_SHIPPED.qvd (qvd);
Drop table SHIPMENTS_AS_DEPLETIONS_SHIPPED;
Use Where exists or may be Left join that table in to this table
Load
SAP_CUST_NO
From Excel;
Left Join
LOAD
COMPANY_CODE,
SALES_ORD_NBR,
SALES_ORD_LINE_NBR,
SAP_CUST_NO,
SALES_ORG,
DIST_CHANNEL,
CUST_PO_NBR,
EDI_ORD_FLAG,
SALES_DOC_TYPE_CD,
ORD_REASON_CD,
ORD_STATUS,
PICKUP_FLAG,
CUST_ORD_DATE,
ORD_DATE,
REQST_DEL_DATE,
PLAN_DEL_DATE,
ACT_DEL_DATE,
SAP_ITEM_NO,
UOM_BASE,
UOM_SELL,
UOM_WGHT,
ITEM_CAT_CD,
SHIP_PLANT,
MAD_DATE,
PLAN_SHIP_DATE,
ACT_SHIP_DATE,
ORD_QTY_BASE,
ORD_QTY_SELL,
ORD_GROSS_WGHT,
ORD_NET_WGHT,
SHIP_QTY_SELL,
SHIP_QTY_BASE,
SHIP_GROSS_WGHT,
SHIP_NET_WGHT,
REJECT_CD
FROM
(
Where Match (ORD_STATUS, 'S');
STORE SHIPMENTS_AS_DEPLETIONS_SHIPPED INTO C:\Users\greenejo\Desktop\Qlikview Development Projects\Depletions Project\QVDs\SHIPMENTS_AS_DEPLETIONS_SHIPPED.qvd (qvd);
Drop table SHIPMENTS_AS_DEPLETIONS_SHIPPED;
Regards
Anand
OK, I see where you are going, but I want to exclude the values on excel spreadsheet, how do I do that since joining will still return the values? New to to Qlikview so I appreciate the help.
Hi,
See this small example in this i want to exclude 1,2,3,4 and only 5 loaded
TabA://Excel Source here
LOAD SAP_NO , SAP_NO AS SAP_A INLINE [
SAP_NO
1
2
3
4 ];
TabB: //Real source Here
LOAD SAP_NO , SAP_NO AS SAP_B
Where Not Exists (SAP_NO);
LOAD * INLINE [
SAP_NO
1
2
3
5 ];
DROP Table TabA;
Regards
Anand
Where exists would do, but with a bit of convolution.
tmp:
Load Distinct SAP_CUST_NO From ListExcel;
tmp2:
Noconcatenate Load Distinct SAP_CUST_NO From SourceExcel
Where Not Exists (SAP_CUST_NO);
Drop Table tmp;
SHIPMENTS_AS_DEPLETIONS_SHIPPED:
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).
Hi,
Ok I got it earlier is mistake now see to exclude the excel sap number, Actually this is your exact load statement i explain with an inline example
TabA: //This is your Excel file
LOAD SAP_CUST_NO, SAP_CUST_NO AS SAP_A INLINE
[
SAP_CUST_NO
1
2
3
4 ];
LOAD
COMPANY_CODE,
SALES_ORD_NBR,
SALES_ORD_LINE_NBR,
SAP_CUST_NO,
SAP_CUST_NO as SAP_B
SALES_ORG,
DIST_CHANNEL,
CUST_PO_NBR,
EDI_ORD_FLAG,
SALES_DOC_TYPE_CD,
ORD_REASON_CD,
ORD_STATUS,
PICKUP_FLAG,
CUST_ORD_DATE,
ORD_DATE,
REQST_DEL_DATE,
PLAN_DEL_DATE,
ACT_DEL_DATE,
SAP_ITEM_NO,
UOM_BASE,
UOM_SELL,
UOM_WGHT,
ITEM_CAT_CD,
SHIP_PLANT,
MAD_DATE,
PLAN_SHIP_DATE,
ACT_SHIP_DATE,
ORD_QTY_BASE,
ORD_QTY_SELL,
ORD_GROSS_WGHT,
ORD_NET_WGHT,
SHIP_QTY_SELL,
SHIP_QTY_BASE,
SHIP_GROSS_WGHT,
SHIP_NET_WGHT,
REJECT_CD
FROM
(
Where Match (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);
Drop table SHIPMENTS_AS_DEPLETIONS_SHIPPED;
Regards
Anand
Tried both examples and neither are working. Is there any other way anyone knows of to exclude lines based on values in an excel sheet?
What if I reversed the process and wanted to include lines based on values vs. exclude in an excel spreadsheet. How would I do that, as it looks like it would be easier?