7 Replies Latest reply: Nov 4, 2014 12:03 PM by Jonathan Greene RSS

    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
      [C:\Users\greenejo\Desktop\Qlikview Development Projects\Depletions Project\SAP Raw Data Tables\VW_F_NAM_SAL_ORD.xlsx]
      (
      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;

       

        • Re: QVD Creation with inclusions and exclusions?
          Anand Chouhan

          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
          [C:\Users\greenejo\Desktop\Qlikview Development Projects\Depletions Project\SAP Raw Data Tables\VW_F_NAM_SAL_ORD.xlsx]
          (
          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



            • 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: Re: QVD Creation with inclusions and exclusions?
                  Jakub Michalik

                  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?
                    Anand Chouhan

                    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
                    [C:\Users\greenejo\Desktop\Qlikview Development Projects\Depletions Project\SAP Raw Data Tables\VW_F_NAM_SAL_ORD.xlsx]
                    (
                    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

                • Re: QVD Creation with inclusions and exclusions?
                  Anand Chouhan

                  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