Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: QVD Creation with inclusions and exclusions?

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

Re: QVD Creation with inclusions and exclusions?

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.

Re: QVD Creation with inclusions and exclusions?

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
Contributor III

Re: Re: QVD Creation with inclusions and exclusions?

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

Re: QVD Creation with inclusions and exclusions?

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

Re: QVD Creation with inclusions and exclusions?

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

Re: QVD Creation with inclusions and exclusions?

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?

Community Browser