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: 
adiarnon
Creator III
Creator III

connect two tables with key and part of the key need to be between dates

i have two tables

CONTACT TABLE-

CONTRCATITEMCOMPANYSTART DATEEND DATE
C1I1AAA12.01.202112.03.2021
C1I2AAA12.03.202112.03.2022
C1I3AAA12.01.202112.03.2021

 AND PURCHASE TABLE-

PURCHASE ORDERITEMCOMPANYORDER DATE
P1I1AAA12.02.2021
P2I2AAA12.06.2021
P3I3AAA12.01.2023

 

I Need to create a key that connect this to table base on item, company and the order date need to be between the start date and the end date of the contract

what the best way to do it?

adi

1 Solution

Accepted Solutions
2 Replies
Saravanan_Desingh

Try this,

SET DateFormat='MM.DD.YYYY';


tab2:
LOAD * INLINE [
    PURCHASE ORDER, ITEM, COMPANY, ORDER DATE
    P1, I1, AAA, 12.02.2021
    P2, I2, AAA, 12.06.2021
    P3, I3, AAA, 12.01.2023
];

tab1:
LOAD * INLINE [
    CONTRCAT, ITEM, COMPANY, START DATE, END DATE
    C1, I1, AAA, 12.01.2021, 12.03.2021
    C1, I2, AAA, 12.03.2021, 12.03.2022
    C1, I3, AAA, 12.01.2021, 12.03.2021
];

Inner Join
IntervalMatch([ORDER DATE], ITEM, COMPANY)
LOAD [START DATE], [END DATE], ITEM, COMPANY
Resident tab1;