4 Replies Latest reply: Mar 23, 2017 11:07 AM by Nicola Faccioli RSS

    How to exclude specific data in the loading script.

    Nicola Faccioli

      I have a data set reporting the Sales:

       

      Transaction:

      LOAD [Month],

          [Sold to Party Number],

          [Sold to Party Name],

          [VAT Number],

          [Sale order Number],

          [Order Quantity],

          [Unit of Measure],

          [Order Value],

          [Delivery Number],

          [Ship to Party],

          [Ship To Name],

          [Good Issue Date],

          [Delivery Quantity],

         [Invoice],

          [Invoice type],

          [Invoice date] as "Date",

          [Bill-to-party Number],

          [Bill-to-party Name],

          [Material Number],

          [Material Description],

          [Invoice Quantity],

          [Billing Document Currency],

          [Unit Price],

          [Net Value],

          [Discount],

          [Freight],

          [Freight (Internal/Statistical)],

          [Tax Amount],

          [Tax Percentage],

         left(subfield(FileName(),'_',1),12) as "Sales_Org"

      FROM [lib://Analisi Dati/*.xls]

      (biff, embedded labels, table is Format$);

       

      Invoice dimension contains only a 10 digit number.

       

      I have created another data set with Invoice which I would like to exclude in the loading phase.

      The new Data set has only one dimension:

      [Invoice]



      I know is a very basic question but I'm new to Qlik Sense and I don't know how to progress.

      Thank you in advance for your help and suggesiton.

       

      Nick

       

        • Re: How to exclude specific data in the loading script.
          Anil Babu Samineni

          Can you explain few values for Invoice from Sales Table and Data set then come up with which values you want to exclude?

          • Re: How to exclude specific data in the loading script.
            Aar Kay

            To my understanding i guess you dont need the invoice numbers that  are in the second table

            if that is the case!

            May be this

            Transaction:

            LOAD [Month],

                [Sold to Party Number],

                [Sold to Party Name],

                [VAT Number],

                [Sale order Number],

                [Order Quantity],

                [Unit of Measure],

                [Order Value],

                [Delivery Number],

                [Ship to Party],

                [Ship To Name],

                [Good Issue Date],

                [Delivery Quantity],

               [Invoice],

                [Invoice type],

                [Invoice date] as "Date",

                [Bill-to-party Number],

                [Bill-to-party Name],

                [Material Number],

                [Material Description],

                [Invoice Quantity],

                [Billing Document Currency],

                [Unit Price],

                [Net Value],

                [Discount],

                [Freight],

                [Freight (Internal/Statistical)],

                [Tax Amount],

                [Tax Percentage],

               left(subfield(FileName(),'_',1),12) as "Sales_Org"

            FROM [lib://Analisi Dati/*.xls]

            (biff, embedded labels, table is Format$);


            Left join

            Load

            Invoice,

            'X' as [Deletion Flag]

            From

                 Dataset;


            NoConcatenate

            Load *

            Resident

                 Transaction

            Where

                 Dataset <>'X';

            Drop Table

                 Transaction;

                

            • Re: How to exclude specific data in the loading script.
              Marcus Sommer

              You could exclude them with a where-exists-clause like the following (a bit simplified here):

               

              InvoicesToExclude:

              load * Invoice from ExcludingSource;

               

              Transactions:

              load * from TransactionSource where not exists(Invoice);

               

              - Marcus

              • Re: How to exclude specific data in the loading script.
                Nicola Faccioli

                Thank you for the help Aar and Marcus.

                Problem solved.

                 

                Cheers

                 

                Nick