Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rizzo
Contributor II
Contributor II

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

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

ItemLocationSelling_dateSelling_price
123A2020-02-031.5
123A2020-02-051.6
243B2020-02-070.9
243B2020-02-094.8

 

B

ItemLocationPurchase_datePrime_cost
123A2020-02-031.1
123A2020-02-011.3
243B2020-02-050.5
243B2020-02-084.1

 

Result

ItemLocationSelling_dateSelling_pricePurchase_datePrime_cost
123A2020-02-031.52020-02-011.3
123A2020-02-051.62020-02-031.1
243B2020-02-070.92020-02-050.5
243B2020-02-094.82020-02-084.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 '|');

1 Solution

Accepted Solutions
Kushal_Chawda

@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;

 

Annotation 2020-09-08 155906.png

View solution in original post

7 Replies
Taoufiq_Zarra

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

243B2020-02-094.82020-02-084.1
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Rizzo
Contributor II
Contributor II
Author

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
Partner - Specialist III
Partner - Specialist III

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:

Screenshot_1.png

Is this what you're looking for?

 

Best regards,

MB

Rizzo
Contributor II
Contributor II
Author

Thank you for you answer but Selling_date should be closest to Purchase_date  like we see on Item 243
Rizzo_0-1599565069648.png

 

miguelbraga
Partner - Specialist III
Partner - Specialist III

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

miguelbraga_0-1599572827936.png

King regards,

MB

Kushal_Chawda

@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;

 

Annotation 2020-09-08 155906.png

Rizzo
Contributor II
Contributor II
Author

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

Thank you very much