Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
cfountain72
Creator
Creator

Left Join Lows

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

 

 

 

Labels (1)
1 Solution

Accepted Solutions
RsQK
Creator
Creator

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.

View solution in original post

2 Replies
RsQK
Creator
Creator

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
Creator
Creator
Author

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