Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
arsenal83
Contributor

Joins with distinguish

Hello,

I'm looking for some help with the issue I've got. I have two tables ORDERS and RECEIPTS. I need to find the first RECEIPT ID that match the order (matched meens the same SHOP+VALUE and RECEIPT DATA >= ORDER_DATE). The problem I've got is that I only can join on SHOP+VALUE and one RECEIPT should be used only once (to the first matched ORDER). Example:

ORDERS:

ORDER_SHOP

ORDER_VALUE

ORDER_DATE

D001

10

01.10.2018

D001

10

15.10.2018

D001

10

15.10.2018

D001

10

01.11.2018

D001

10

24.12.2018

D001

15

11.10.2018

D002

20

11.10.2018

D003

10

15.10.2018

 

RECEIPTS:

REC_NO

REC_DATE

REC_SHOP

REC_VALUE

P_001

02.09.2018

D001

10

P_002

30.10.2018

D001

10

P_003

30.10.2018

D001

10

P_004

25.11.2018

D001

10

P_005

30.10.2018

D001

15

 

Result I Expect:

ORDER_SHOP

ORDER_VALUE

ORDER_DATE

REC_NO

REC_DATE

D001

10

01.10.2018

P_002

30.10.2018

D001

10

15.10.2018

P_003

30.10.2018

D001

10

15.10.2018

P_004

25.11.2018

D001

10

01.11.2018

 

 

D001

10

24.12.2018

 

 

D001

15

11.10.2018

P_005

30.10.2018

D002

20

11.10.2018

 

 

D003

10

15.10.2018

 

 

Tags (2)
1 Solution

Accepted Solutions
chrismarlow
Valued Contributor

Re: Joins with distinguish

Hi,
 
I started this thinking although tricky there would be a neat solution in the end … the attached does work, but is a bit longer than I thought it would be;
20190109_2.png
 
I am not sure if the IntervalMatch (61-67) simplifies or not - I thought that might be able to avoid using the loop (92-116), by using it but ended up looping through the data anyway.
 
Post back if you cannot open the file (I have personal edition) - I can post up the code.
 
Cheers,
 
Chris

View solution in original post

2 Replies
chrismarlow
Valued Contributor

Re: Joins with distinguish

Hi,
 
I started this thinking although tricky there would be a neat solution in the end … the attached does work, but is a bit longer than I thought it would be;
20190109_2.png
 
I am not sure if the IntervalMatch (61-67) simplifies or not - I thought that might be able to avoid using the loop (92-116), by using it but ended up looping through the data anyway.
 
Post back if you cannot open the file (I have personal edition) - I can post up the code.
 
Cheers,
 
Chris

View solution in original post

arsenal83
Contributor

Re: Joins with distinguish

Hi, 

thanks a lot! 

great solution!