Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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??