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.
faccioli
New 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
Valued Contributor

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

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;

    

4 Replies

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

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

Life is so rich, and we need to respect to the life !!!
aarkay29
Valued Contributor

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

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.

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

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

Thank you for the help Aar and Marcus.

Problem solved.

Cheers

Nick

Community Browser