Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
brf10043
Creator
Creator

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]);

Labels (3)
2 Replies
Saravanan_Desingh

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

Brett_Bleess
Employee
Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.