topic Re: Data load exclusions required in QlikView

Data load exclusions required

davyqliks — Wed, 17 Apr 2019 09:18:27 GMT

Thank you for taking the time to read this.

We have Data which is included in the load by reference to a coa code.

The COA code ([Account Code]) is in the main load for all values.

i have a list of codes in a secondary excel from which i can only load the data from the main load if matched in the lookup.

I have managed to filter the data with a button but i do not want to import the un required data at all.

I have been given a new code to add to the list of inclusions but only one of the descriptions from that code is required in the load.

Please assist in the best way to.

1. Only load data from the main load where COA code is matched data from a secondary excel
2. Make a 2nd filter on description and only load this information for that distinct code.

I need to only import main data where given values in the Account code are present in the lookup, [Account Code] And Where the account code = 99999999999 and the description = Sales minimum charges are matched from the lookup to the main load.

MAIN LOAD

LOAD rowno(),

'CHK' as Location,
[Retro in Base Currency],
[Invoice no.],
[Voucher Date],
Year([Voucher Date]) as [INV Year],
Month([Voucher Date]) as [Inv Month],
Day([Voucher Date]) as [Inv Day],
Date(monthstart([Voucher Date]), 'MMM-YYYY') as MonthYear,
[Brand Name],
[Retailer Name],
[Invoicing Customer No.],
[Invoicing Customer name],
[Customer Invoicing Country],
[Item type],
[Item No.],
[Item Name],
[Invoice Item Line Quantity],
[Invoice Currency],
[Invoice Item Unit Price],
[Invoice Item Amount],
[VAT Amount],
[Invoice Item Total in Invoice Currency],
[Base Currency],
[Invoice Item Amount in Base Currency],
[VAT Amount in Base Currency],
[Invoice Item Total in Base Currency],
[Invoice Item Amount in Base Currency]* ApplyMap('Map_Currency_Rate1', [Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY')) AS GBP_Price,
[Invoice Item Amount in Base Currency]* ApplyMap('Map_Currency_Rate2', [Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY')) AS HKD_Price,
[Invoice Payment Terms],
[Account Code]

FROM
[**********.xlsx]
(ooxml, embedded labels, header is 6 lines, table is CHK)WHERE(not IsNull([Invoice no.]));

LOOKUP LOAD

[COA MAP]:
LOAD
//Location,
[Account Code],
Description
FROM
[********************.xlsx]
(ooxml, embedded labels, table is [COA map]);

Thank you.

Re: Data load exclusions required

swuehl — Wed, 17 Apr 2019 15:08:16 GMT

Load your lookup table first in the script, then add an WHERE clause with EXISTS() to your main data load:

...

FROM
[**********.xlsx]
(ooxml, embedded labels, header is 6 lines, table is CHK) WHERE (not IsNull([Invoice no.]))

and Exists([Account Code]);

Re: Data load exclusions required

davyqliks — Thu, 18 Apr 2019 07:44:17 GMT

Thanks, i will give that a shot today.

Re: Data load exclusions required

davyqliks — Thu, 18 Apr 2019 08:49:05 GMT

Thank you for taking the time to look at this, appreciate!

So,

I have the load like so. no errors

LOAD 'LUK' as Location,
[Retro in Base Currency],
[Invoice no.],
[Voucher Date],
Year([Voucher Date]) as [INV Year],
Month([Voucher Date]) as [Inv Month],
Day([Voucher Date]) as [Inv Day],
Date(monthstart([Voucher Date]), 'MMM-YYYY') as MonthYear,
[Brand Name],
[Retailer Name],
[Customer No.] as [Invoicing Customer No.],
[Customer name] as [Invoicing Customer name],
[Customer Invoicing Country],
[Item type],
[Item No.],
[Item Name],
[Invoice Item Line Quantity],
[Invoice Currency],
[Invoice Item Unit Price],
[Invoice Item Amount],
[VAT Amount],
[Invoice Item Total in Invoice Currency],
[Base Currency],
[Invoice Item Amount in Base Currency],
[VAT Amount in Base Currency],
[Invoice Item Total in Base Currency],
[Invoice Item Amount in Base Currency]* ApplyMap('Map_Currency_Rate1', [Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY')) AS GBP_Price ,
[Invoice Item Amount in Base Currency]* ApplyMap('Map_Currency_Rate2', [Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY')) AS HKD_Price,
[Invoice Payment Terms],
[Account Code]
FROM
[Q:******** UK*.xlsx]
(ooxml, embedded labels, header is 5 lines, table is LUK)WHERE EXISTS ([Item No],('Sales minimum charges')) and (not IsNull([Invoice no.]));

This is concatenated with other tables with the same fields.

Now i get all the data but none from the 'LUK' as Location load.

Can you point out why this might be, thanks again.

Re: Data load exclusions required

swuehl — Thu, 18 Apr 2019 10:57:04 GMT

Your syntax would require that field [Item No.] has been loaded prior to the LOAD statement you've posted and that it does hold a literal value 'Sales minimum charges'.

Looks to me that this would result in a WHERE clause that checks a condition that is constant for the whole LOAD statement, so I wouldn't write it like this, but I assume you intend something else.

Re: Data load exclusions required

davyqliks — Tue, 23 Apr 2019 14:19:05 GMT

Hi,

Thank you again for your help, it is much appreciated:
Here is the lookup code loaded before the main load

[COA MAP]:
LOAD
//Location,
//[Account Code],
[Item Name]
FROM
[**********\Qlikview mapping_V2.xlsx]
(ooxml, embedded labels, table is [COA map]);

In this load the lookup contains the column [Item Name] and it has the value 'Sales minimum charges'

now the main load is as per below:

LOAD 'LUK' as Location,
[Retro in Base Currency],
[Invoice no.],
[Voucher Date],
Year([Voucher Date]) as [INV Year],
Month([Voucher Date]) as [Inv Month],
Day([Voucher Date]) as [Inv Day],
Date(monthstart([Voucher Date]), 'MMM-YYYY') as MonthYear,
[Brand Name],
[Retailer Name],
[Customer No.] as [Invoicing Customer No.],
[Customer name] as [Invoicing Customer name],
[Customer Invoicing Country],
[Item type],
[Item No.],
[Item Name],
[Invoice Item Line Quantity],
[Invoice Currency],
[Invoice Item Unit Price],
[Invoice Item Amount],
[VAT Amount],
[Invoice Item Total in Invoice Currency],
[Base Currency],
[Invoice Item Amount in Base Currency],
[VAT Amount in Base Currency],
[Invoice Item Total in Base Currency],
[Invoice Item Amount in Base Currency]* ApplyMap('Map_Currency_Rate1', [Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY')) AS GBP_Price ,
[Invoice Item Amount in Base Currency]* ApplyMap('Map_Currency_Rate2', [Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY')) AS HKD_Price,
[Invoice Payment Terms],
[Account Code]
FROM
[Q:\QVFiles\Group Sales\Labelon UK*.xlsx]
(ooxml, embedded labels, header is 5 lines, table is LUK)WHERE EXISTS ([Item Name],('Sales minimum charges')) and (not IsNull([Invoice no.]));

I am getting a return now for the UK but receiving All returns for [Item Name] from the [COA MAP] & the LUK load.

Thank you for your help but i feel back to the drawing board on this one.

I have renamed the 'description' in the coa lookup to [Item Name] so that there is a match to the main load. 'Sales minimum charges' exists as an item name in both but i now realise if i do this and it works, it will omit all Item name other than 'Sales minimum charges' in the UK load which will omit more than required.

I have a list of COA's for the UK with the value 99999999999, all with different descriptions.

Initially i was asked to match and load the coa data by code, which i did but now as well as this i am asked to only load data for UK where COA=99999999999 and the description [Sales minimum charges]. i have changed description to [Item Name] in the lookup but now feel that i will omit all data other than this for item name in the uk.

Will it be better to load all other than a given list?

Thank you, I will understand if you drop this thread.

Thanks