Joining two tables by two fields and geting only the last value from joined table where

[Rizzo]

Hi, I got stuck :(.
I have  two tables -  A and B, need to join them by two fields - Item  and Location 
where the B.Purchase_date  < A.Selling_date  and get only one the last ( or max) Prime_cost value from table B
Can someone help me?

 A

Item	Location	Selling_date	Selling_price
123	A	2020-02-03	1.5
123	A	2020-02-05	1.6
243	B	2020-02-07	0.9
243	B	2020-02-09	4.8

B

Item	Location	Purchase_date	Prime_cost
123	A	2020-02-03	1.1
123	A	2020-02-01	1.3
243	B	2020-02-05	0.5
243	B	2020-02-08	4.1

Result

Item	Location	Selling_date	Selling_price	Purchase_date	Prime_cost
123	A	2020-02-03	1.5	2020-02-01	1.3
123	A	2020-02-05	1.6	2020-02-03	1.1
243	B	2020-02-07	0.9	2020-02-05	0.5
243	B	2020-02-09	4.8	2020-02-08	4.1

A:
Load * Inline [
Item|Location|Selling_date|Selling_price
123|A|2020-02-03|1.5
123|A|2020-02-05|1.6
243|B|2020-02-07|0.9
243|B|2020-02-09|4.8
]
(delimiter is '|');

B:
Load * Inline [
Item|Location|Purchase_date|Prime_cost
123|A|2020-02-03|1.1
123|A|2020-02-01|1.3
243|B|2020-02-05|0.5
243|B|2020-02-08|4.1
]
(delimiter is '|');

[Taoufiq_Zarra]

@Rizzo  can you elaborate, for example how you get this line ?

243

B

2020-02-09

4.8

2020-02-08

4.1

[Rizzo]

in this case  A.Selling_Date is equal 2020-02-09 then  B.Purchase_Date should last purchase of the Item  -> 2020-02-08,  and  Prime_cost 4.1

[miguelbraga]

Hi there,

One possible option is using this snip code:

A:
Load * Inline [
Item|Location|Selling_date|Selling_price
123|A|2020-02-03|1.5
123|A|2020-02-05|1.6
243|B|2020-02-07|0.9
243|B|2020-02-09|4.8
]
(delimiter is '|');

B:
Load * Inline [
Item|Location|Purchase_date|Prime_cost
123|A|2020-02-03|1.1
123|A|2020-02-01|1.3
243|B|2020-02-05|0.5
243|B|2020-02-08|4.1
]
(delimiter is '|');

Temp:
NoConcatenate
LOAD
Item&'-'&Location&'-'&RowNo() as Key,
Selling_date,
Selling_price
Resident A;

Outer join (Temp)

LOAD
Item&'-'&Location&'-'&RowNo() as Key,
Purchase_date,
Prime_cost
Resident B;

Drop Tables A, B;

Result:
LOAD
SubField(Key, '-', 1) as Item,
SubField(Key, '-', 2) as Location,
Selling_date,
Selling_price,
Purchase_date,
Prime_cost
Resident Temp;

Drop Table Temp;

To produce this result:

Is this what you're looking for?

Best regards,

MB

[Rizzo]

Thank you for you answer but Selling_date should be closest to Purchase_date  like we see on Item 243

[miguelbraga]

Try this please:

A:
Load * Inline [
Item|Location|Selling_date|Selling_price
123|A|2020-02-03|1.5
123|A|2020-02-05|1.6
243|B|2020-02-07|0.9
243|B|2020-02-09|4.8
]
(delimiter is '|');

B:
Load * Inline [
Item|Location|Purchase_date|Prime_cost
123|A|2020-02-03|1.1
123|A|2020-02-01|1.3
243|B|2020-02-05|0.5
243|B|2020-02-08|4.1
]
(delimiter is '|');

Temp:
NoConcatenate
LOAD
Item&'-'&Location&'-'&RowNo() as Key,
Selling_date,
Selling_price
Resident A
Order by Selling_date;

Outer join (Temp)

LOAD
Item&'-'&Location&'-'&RowNo() as Key,
Purchase_date,
Prime_cost
Resident B
Order by Purchase_date;

Drop Tables A, B;

Result:
LOAD
SubField(Key, '-', 1) as Item,
SubField(Key, '-', 2) as Location,
Selling_date,
Selling_price,
Purchase_date,
Prime_cost
Resident Temp;

Drop Table Temp;

It will give you what you want

King regards,

MB

[Kushal_Chawda · Accepted solution]

@Rizzo  try below

A:
Load * Inline [
Item|Location|Selling_date|Selling_price
123|A|2020-02-03|1.5
123|A|2020-02-05|1.6
243|B|2020-02-07|0.9
243|B|2020-02-09|4.8
]
(delimiter is '|');

Join(A)
Load * Inline [
Item|Location|Purchase_date|Prime_cost
123|A|2020-02-03|1.1
123|A|2020-02-01|1.3
243|B|2020-02-05|0.5
243|B|2020-02-08|4.1
]
(delimiter is '|');

Inner join(A)
LOAD Item,
     Location,
     Selling_date,
     date(max(if(Purchase_date<Selling_date,Purchase_date))) as Purchase_date
Resident A
Group by Item,Location,Selling_date;

[Rizzo]

I tried it on my table with ~ 1,000,000 entries and I like the result!

Thank you very much