Left Join - very unique!!

[qlikviewaf]

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

[edwin]

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.

[MayilVahanan · Accepted solution]

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: