Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
faccioli
Contributor
Contributor

How to exclude specific data in the loading script.

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

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

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;

    

View solution in original post

4 Replies
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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
aarkay29
Specialist
Specialist

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

Thank you for the help Aar and Marcus.

Problem solved.

Cheers

Nick