Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QVD Creation with inclusions and exclusions?


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

(
ooxml, embedded labels, table is Data)

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;

7 Replies
its_anandrjs

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

(
ooxml, embedded labels, table is Data)

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



Not applicable
Author

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.

its_anandrjs

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


kuba_michalik
Partner - Specialist
Partner - Specialist

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

its_anandrjs

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

(
ooxml, embedded labels, table is Data)
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

Not applicable
Author

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?

Not applicable
Author

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?