- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Flag non matches in a Right Join Load Script
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]);
- Tags:
- qlikview_scripting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can try either ApplyMap or Not Exists in Where, to check the availability.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.