Left Join Lows

[cfountain72]

Hello,

This seems like an easy thing to do, but I'm stuck. I want to add a value (PAT_ENC_CSN_ID) found in one table (ENCOUNTERS) to another (ESCALATIONS), using a where clause. (In SQL, I would use a left join, but these tables have different data sources, so that's not feasible.)

ENCOUNTERS:

Load PAT_ENC_CSN_ID,

MRN,

Admission_Dttm,

Discharge_Dttm;

ESCALATIONS:

Load

MRN,

Escalation_Dttm;

Left Join 

Load

PAT_ENC_CSN_ID

Resident ENCOUNTERS

where Escalation_Dttm >= Admission_Dttm

and Escalation_Dttm <= Discharge_Dttm;

Naturally, I get an error because ENCOUNTERS doesn't have Escalation_Dttm  in it. Any suggestions?

Thanks,

Chris

[RsQK · Accepted solution]

Hi, you should use intervalmatch for this. I'm guessing the dimension "MRN" is a common between both tables, so the script would look like this:

ENCOUNTERS:
Load 
PAT_ENC_CSN_ID,
MRN,
Admission_Dttm,
Discharge_Dttm INLINE
[
PAT_ENC_CSN_ID,MRN,Admission_Dttm,Discharge_Dttm
1,A,20.03.2022,23.03.2022
2,B,21.03.2022,22.03.2022
3,C,22.03.2022,25.03.2022
4,C,20.03.2022,21.03.2022
5,B,24.03.2022,25.03.2022
6,A,21.03.2022,25.03.2022
];

ESCALATIONS:
Load
MRN,
Escalation_Dttm INLINE 
[
MRN,Escalation_Dttm
A,21.03.2022
B,24.03.2022
C,22.03.2022
];

INTERVALMATCH(Escalation_Dttm,MRN)
LEFT JOIN (ESCALATIONS)
LOAD DISTINCT
Admission_Dttm,
Discharge_Dttm,
MRN
RESIDENT ENCOUNTERS;

LEFT JOIN (ESCALATIONS)
LOAD DISTINCT
Admission_Dttm,
Discharge_Dttm,
MRN,
PAT_ENC_CSN_ID AS PAT_ENC_CSN_ID_JOINED
RESIDENT ENCOUNTERS;

DROP FIELDS Admission_Dttm,Discharge_Dttm FROM ESCALATIONS;

 If the "MRN" isn't needed for the join, just remove it from intervalmatch and left join.

[cfountain72]

Thank you so much! This gift of knowledge was extremely useful, and was obviously a little more complicated than I was expecting.