Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi helpful people,
How can I exclude data from the load where fields match ?
here is the context
I have one table of orders and a table of orders that have shipped.
in both tables I create a Unique id from the order number and the item line.
In excel a vlookup of the UID from the orders table to the shipment table and then delete the matches
So back in the Qlik world
where UID and UID2 are the same I have no need to load this data
Open to Ideas 🙂
Accepted Solutions
I will do my best to explain
I have a table of orders TABLE 1, this table consists of an Order Number (SO) and a Item (Sales Document Item) Concatenated these form the UID
Now this table contains all orders regardless of whether the orders have been shipped or not.
The second table (TABLE 2) I have a list of proposed shipments, some have shipped and some have not.
This table also includes the same UID logic
First step is to delete the rows from TABLE 2 where the row does not have a date
// this loads the data of what has shipped
ZSHIP:
LOAD
"Order",
Item,
"Order"&Item as UID2,
"Delivery PGI date"
FROM [lib://BI/ZSHIP.xlsx]
(ooxml, embedded labels, table is Format)
// this is filtering out where shipments havent take place
where
len("Delivery PGI date">1);I accomplish this by evaluating the length of the field of the date as above.
Now the second stage is to evaluate which rows in both TABLE 1 and TABLE 2 have the same UID if they do, we do not need to load them.
No other data is required from TABLE 2 its purpose is simply to reduce the rows in TABLE 1.
From the static report I am looking to automate with this App the instruction is as follows
1) Delete all rows (Table 2) which do not have a date
2) Concatanate Column A and B to create UID
3) In TABLE 1, Vlookup against the UID created in column TABLE 2.
4) Delete any matches that occur between the 2 sheets.
Full Script
[Format]:
LOAD
[SO],
[Sales Document Item],
[Material],
[Item Description],
[Item Qty],
[Contract Admin],
[Ship to Name],
[Ship to Account],
[Ship to City],
[Ship To],
[First Date],
[Item Release Status],
[Ship Request No],
[Delivery Note],
[SO]&[Sales Document Item] as UID,
APPLYMAP( '__cityKey2GeoPoint', APPLYMAP( '__countryCodeAndCityName2Key', APPLYMAP( '__countryName2IsoThree', LOWER([Ship To])) & LOWER([Ship to City])), '-') AS [Format.Ship to City_GeoInfo],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Ship To])), '-') AS [Format.Ship To_GeoInfo]
FROM [lib://BI/GCO TAB.xlsx]
(ooxml, embedded labels, table is Format)
// ADD FILTERS FROM gco report data to the where clause
// [Item Release Status] = 'Not Released' - filters out orders that has not yet been released
// [First Date] <= today(1)+90 - limits the view to orders within the next three months.
Where
[Item Release Status] = 'Not Released'
and
[First Date] <= today(1)+90
;
// this loads the data of what has shipped
ZSHIP:
LOAD
"Order",
Item,
"Order"&Item as UID2,
"Delivery PGI date"
FROM [lib://BI/ZSHIP.xlsx]
(ooxml, embedded labels, table is Format)
// this is filtering out where shipments havent take place
where
len("Delivery PGI date">1)
;
FORMAT = TABLE 1
ZSHIP = TABLE 2
