topic Re: Flag non matches in a Right Join Load Script in QlikView

Flag non matches in a Right Join Load Script

brf10043 — Sat, 16 Nov 2024 18:46:56 GMT

Team, I have the below code that right joins data from an Excel file to an existing data set. The result is that only data that matches the CS_MODEL_NUMBER below is kept. The challenge that I am having is that because the Excel file is manager by humans it is possible for them to enter an incorrect CS_MODEL_NUMBER. Is there a way for me to identify and flag CS_MODEL_NUMBER that came from the Excel and didn't match any existing CS_MODEL_NUMBER? Then I could produce a count KPI and export table of the non matches

////CS Tower
Right Join
LOAD [Tower name] as [CS Tower],
[Model_Number (from Pandora)] as [CS Model],
[Model_Number (from Pandora)]as CS_MODEL_NUMBER,
[End Of Life Status (Yes / No )] as [Eol Status],
DATE([End Of Life Status Date (day/mon/year)],'DD-MMM-YYYY')as [End Of Life Status Date],

If(Len(Trim([End Of Life Status Date (day/mon/year)])) = 0, 'No End of Life Date',
If([End Of Life Status Date (day/mon/year)] < Today(), 'Past EoL',
If([End Of Life Status Date (day/mon/year)] <= Today()+365, 'Within 12 Months',
If([End Of Life Status Date (day/mon/year)] <= Today()+730, 'Within 24 Months','Beyond 24 Months'
)))) as [Duration to EoL]

FROM
[$(VExternalDirectory)EoL Tower data.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

Re: Flag non matches in a Right Join Load Script

Saravanan_Desingh — Wed, 29 Apr 2020 17:38:51 GMT

You can try either ApplyMap or Not Exists in Where, to check the availability.

Re: Flag non matches in a Right Join Load Script

Brett_Bleess — Thu, 14 May 2020 14:26:19 GMT

If Saran's post got you what you needed, we would appreciate it if you could return to your thread and use the Accept as Solution button on that post to mark it giving them credit for the help and letting other Members know that worked.

Here is a link that may be of some further help along the lines of that post:

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

The only other thing of which I can think would be to load the Excel file into a temp resident table, sort/order it and then you could Peek() each ID to check if it exists in the other table etc, and if not, you could use a flag field to set that flag to a '1' else 0 to be able to dump those records out etc.

Regards,
Brett