Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All!
i have a sales table like follow - very simple: Date/Customer/Item/Amount
Then i have another table that i wolud like to left join based on Customer+Item+Date , in order to assign the value "Gara" to the sales table.
For each line on sales table i would like to assign just one line of the second table.
This is the result i would like to achieve. The left join should occur based on customer and item, but also looking the date. Assign value "Gare" only if date is equal or greater to Date(on sales table) - then if for the same item&customer a new record exist on the second table, the most recent needs to be applied just from that specific date on.
To better clarify, here is the result i would like to get. I tried something on the Qvw attached but didnt get what i want.
Anyone can please help? Thanks!
Date | Customer | Item | Amount | Gara |
01/02/2021 | Liz | A | 100 | |
02/03/2021 | Liz | A | 200 | 1291 |
02/03/2021 | Liz | A | 300 | 1291 |
22/03/2021 | Liz | A | 400 | 1291 |
11/04/2021 | Liz | D | 500 | 1291 |
01/05/2021 | Liz | D | 500 | TESI9 |
21/05/2021 | Liz | D | 500 | TESI9 |
01/02/2021 | Tony | A | 100 | |
02/03/2021 | Tony | A | 200 | |
02/03/2021 | Tony | A | 300 | |
22/03/2021 | Tony | A | 400 | |
20/04/2021 | Tony | Z | 500 | TEST |
10/05/2021 | Tony | Z | 500 | ABORS |
30/05/2021 | Tony | Z | 500 | ABORS |
Hi @qlikviewaf
For Item D for Liz, there is no offering info, then how you were mapped with Gara?
If you've missed the offer for D, then try like below
Sales:
LOAD Date,Customer, Item, Amount, Customer&Item as Key
FROM [Sales Data Gare.xlsx]
(ooxml, embedded labels, table is Sales);
Offering:
LOAD
Customer&Item as Key,
Date,
Gara
FROM
[D:\Qlik\Com\Sales Data Gare.xlsx]
(ooxml, embedded labels, table is Offering);
OfferingTemp:
Load Key, Date as StartDate, Gara, If(Key = Previous(Key), Date(Peek('StartDate')-1), Date(Yearend(Today()))) as EndDate Resident Offering
order by Key, Date desc;
DROP Table Offering;
Final:
IntervalMatch(Date, Key)
Load StartDate, EndDate, Key Resident OfferingTemp;
Join(Final)
LOAD * Resident Sales;
Left Join(Final)
Load * Resident OfferingTemp;
DROP Table OfferingTemp, Sales;
o/p:
the rule re the dates is a bit confusing. my understanding is to find the newest date from table 2 for the same Customer and Item
however in your desired output, 01/02/2021 does not have a Gara found for it.
Hi @qlikviewaf
For Item D for Liz, there is no offering info, then how you were mapped with Gara?
If you've missed the offer for D, then try like below
Sales:
LOAD Date,Customer, Item, Amount, Customer&Item as Key
FROM [Sales Data Gare.xlsx]
(ooxml, embedded labels, table is Sales);
Offering:
LOAD
Customer&Item as Key,
Date,
Gara
FROM
[D:\Qlik\Com\Sales Data Gare.xlsx]
(ooxml, embedded labels, table is Offering);
OfferingTemp:
Load Key, Date as StartDate, Gara, If(Key = Previous(Key), Date(Peek('StartDate')-1), Date(Yearend(Today()))) as EndDate Resident Offering
order by Key, Date desc;
DROP Table Offering;
Final:
IntervalMatch(Date, Key)
Load StartDate, EndDate, Key Resident OfferingTemp;
Join(Final)
LOAD * Resident Sales;
Left Join(Final)
Load * Resident OfferingTemp;
DROP Table OfferingTemp, Sales;
o/p: