Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I can't get my IntervalMatch working. Here's my script text:
Try this adjusted query (move the join of query to the row before intervalmatch instead of the row after.
LEFT JOIN
IntervalMatch(SNO_START_DATE, EMPLOYEE_ID)
LOAD X_START, X_END, EMPLOYEE_ID Resident xactly;
Thanks so much Vegar! Unfortunately, it is the same result with X_START & X_END not populating.
X_START & X_END are not getting set, although the structure looks better now:
if possible can you share your share so that its easy to debug.....
in this scenario need to check how 2 tables data are related.
if we have matching data in the both tables as per conditions then you will get data in X_START & X_END fields
@anat Here is a modified load script using inline tables:
snowflake:
LOAD * INLINE [
EMPLOYEE_ID, SNO_START_DATE
106056, 1/1/1970
106056, 2/1/2019
106056, 2/1/2020
106056, 2/1/2021
106056, 2/1/2022
106056, 5/1/2022
106056, 2/1/2023
106056, 7/1/2023
108950, 1/1/1970
108950, 8/1/2020
108950, 2/1/2021
108950, 2/1/2022
108950, 2/1/2023
108950, 11/1/2023
];
xactly:
LOAD * INLINE [
EMPLOYEE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
106056, 1/1/1970, 1/31/2019
106056, 2/1/2019, 1/31/2020
106056, 2/1/2020, 1/31/2021
106056, 2/1/2021, 1/31/2022
106056, 2/1/2022, 4/30/2022
106056, 5/1/2022, 1/31/2023
106056, 2/1/2023, 6/30/2023
106056, 7/1/2023, 12/31/2035
108950, 1/1/1970, 7/31/2020
108950, 8/1/2020, 1/31/2021
108950, 2/1/2021, 1/31/2022
108950, 2/1/2022, 1/31/2023
108950, 2/1/2023, 10/31/2023
108950, 11/1/2023, 12/31/2035
];
left Join
IntervalMatch ( SNO_START_DATE, EMPLOYEE_ID )
LOAD X_START, X_END, EMPLOYEE_ID
Resident xactly;
Well aside from the typos, it's now working. Now I just need my files to work as well!