IntervalMatch on Dates with Emp_Id

[juliestephensusa]

I can't get my IntervalMatch working. Here's my script text:

xactly:

LOAD DISTINCT

Text([Participant Employee ID]) as EMPLOYEE_ID,

     Date([Effective Start Date]) AS X_START, 

     Date([Effective End Date]) AS X_END

FROM

[C:\Users\julie\Downloads\Personversions.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

snowflake:

LOAD DISTINCT

Text(EMP_ID_TXT) as EMPLOYEE_ID,

Date(EFFECTIVE_START_DATE) AS SNO_START_DATE

FROM

[C:\Users\julie\Downloads\xc_participant reconciliation_js.xlsx]

(ooxml, embedded labels, table is Snowflake);

IntervalMatch(SNO_START_DATE, EMPLOYEE_ID) LEFT JOIN LOAD X_START, X_END, EMPLOYEE_ID Resident xactly;

LEFT JOIN (snowflake) LOAD * Resident snowflake;

and this is what my output is showing: Why are my x_start & x_end dates not getting applied to my dataset?

[Vegar]

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;

[juliestephensusa]

Thanks so much Vegar! Unfortunately, it is the same result with X_START & X_END not populating.

[anat · Accepted solution]

snowflake:

LOAD DISTINCT

Text(EMP_ID_TXT) as EMPLOYEE_ID,

Date(EFFECTIVE_START_DATE) AS SNO_START_DATE

FROM

[C:\Users\julie\Downloads\xc_participant reconciliation_js.xlsx]

(ooxml, embedded labels, table is Snowflake);

xactly:

LOAD DISTINCT

Text([Participant Employee ID]) as EMPLOYEE_ID,

     Date([Effective Start Date]) AS X_START, 

     Date([Effective End Date]) AS X_END

FROM

[C:\Users\julie\Downloads\Personversions.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

left Join

IntervalMatch ( SNO_START_DATE, EMPLOYEE_ID )

LOAD X_START, X_END, EMPLOYEE_ID

Resident xactly;

[juliestephensusa]

X_START & X_END are not getting set, although the structure looks better now:

[anat]

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

[juliestephensusa]

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

[juliestephensusa]

Well aside from the typos, it's now working. Now I just need my files to work as well!