Explain : more records after left join

ashmitp869 — Thu, 06 May 2021 03:29:38 GMT

Hi there,

please kindly explain why I am getting more records after left join , below I provided the script as well.

Amber Care table got : 434 records and dim facility got :22 records but Amber care Final table has : 9548 .. Any help ...pls kindly explain what I am doing wrong.

script :

[Amber Care]:
LOAD
FACILITY,
If(FACILITY like '*Ho*', 'Ho',
If(FACILITY like '*Ro*', 'R',
If(FACILITY like '*Ry*', 'Ry',
If(FACILITY like '*Green*', 'Green',
If(FACILITY like '*Mo*', 'Mo',
If(FACILITY like '*Ne*', 'Ne','FACILITY')))))) as facility_name,
MRN
FROM [lib://AttachedFiles/Data.xlsx]
;

Unqualify *;

left join ([Amber Care])
LOAD "facility_identifier" ,
facility_name

SQL SELECT *
FROM "view";

Unqualify *;

[Amber Care Final]:
Load "facility_identifier" & '-' & "VISIT_ID" as VISITID,
*
Resident [Amber Care];

Drop Table [Amber Care];

Thanks

Re: Explain : more records after left join

eddie_wagt — Thu, 06 May 2021 14:17:02 GMT

It looks like the join is not correct. It joins on facility name and not on a unique ID/Key. Therefore you see that 434 * 22 = 9548 lines. It now joins on all possibilities. If you want to fix this then you should look for a key that is unique enough. Maybe the field "facility_identifier" is the same as "FACILITY" or is facility_name exact the same as facility_name in the first load??

topic Explain : more records after left join in App Development

Explain : more records after left join

Re: Explain : more records after left join