Left join with master file using File Loop

[richard24best]

Hi Team,

I have a master table called MPR_Fact to which i want to do left join with Year end files Dec_2021, Dec_2022,Dec_2023 using ID and create flag

For Each File in FileList('[lib://ArchiveYear/Dec*]')
left join
YearEnd:
LOAD

distinct "ID",'Y' as Remediated_Flag

FROM [$(File)] (ooxml, embedded labels) where match("Project Status (M)",'Process demised',
'Remediated - Fully automated');
Next File

Could you please check and advice, how do i get all the IDs in year end files to be tagged with Y in the main Table?

Appreciate your time and support in advance.

Regards,

Richard

[anat]

Here is the similar join in loop

https://community.qlik.com/t5/QlikView-App-Dev/Left-join-inside-loop/td-p/362930

[marcus_sommer]

Joining within loops will seldom be working because the added field from the first iteration becomes a KEY in the second iteration. Therefore remove the join and load all information in a temp-table, afterwards you creates a mapping table from the temp-table and in a third step you load the origin table with an applymap() to get your flag-value and setting also a default-value in the third parameter for the non-matchings.

Beside this if your id's aren't unique in regard to the year you will need to add this information, too for the mapping.

[steeefan]

You need to combine your Dec_2021, Dec_2022,Dec_2023 files before joining them to the master table. Otherwise, after the first JOIN, the field Remediated_Flag already exists in the master table.

One possible solution:

Archive:
LOAD
 *
FROM
  [lib://ArchiveYear/Dec*.xlsx]
  (ooxml, embedded labels)
WHERE
  match("Project Status (M)", 'Process demised', 'Remediated - Fully automated');

LEFT JOIN (YearEnd) LOAD
  //...
RESIDENT
  Archive;

DROP TABLE Archive;