Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have two tables
CONTACT TABLE-
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 |
AND PURCHASE TABLE-
PURCHASE ORDER | ITEM | COMPANY | ORDER DATE |
P1 | I1 | AAA | 12.02.2021 |
P2 | I2 | AAA | 12.06.2021 |
P3 | I3 | AAA | 12.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
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;
Output: