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

How to exclude specific data in the loading script.

faccioli — Wed, 22 Mar 2017 22:48:52 GMT

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 — Thu, 23 Mar 2017 01:28:09 GMT

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.

aarkay29 — Thu, 23 Mar 2017 03:33:31 GMT

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 — Thu, 23 Mar 2017 06:22:01 GMT

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.

faccioli — Thu, 23 Mar 2017 15:07:15 GMT

Thank you for the help Aar and Marcus.

Problem solved.

Cheers

Nick