How to exclude specific data in the loading script.

[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

[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?

[aarkay29 · Accepted solution]

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;

[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

[faccioli]

Thank you for the help Aar and Marcus.

Problem solved.

Cheers

Nick