topic Re: Left Join Lows in App Development

Left Join Lows

cfountain72 — Thu, 24 Mar 2022 14:21:10 GMT

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

Re: Left Join Lows

RsQK — Fri, 25 Mar 2022 10:27:22 GMT

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.

Re: Left Join Lows

cfountain72 — Fri, 25 Mar 2022 13:37:40 GMT

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