Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Here is the similar join in loop
https://community.qlik.com/t5/QlikView-App-Dev/Left-join-inside-loop/td-p/362930
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.
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;