Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
FrankGrimm
Partner - Creator
Partner - Creator

find a contend in a loaded field

Hello together,

 

i have 2 loads:

 

_Checker:

load upper(CHECKER) as CHECKER Inline [

CHECKER

Tom

John

Lisa

];

 

_EMPLOYEE:

load Upper(EMPLOYEE) as EMPLOYEE inline [

EMPLOYEE

'Tom Smith'

' Marie Lisa Walter'

'Will Smith'

];

How can i find out, whether the names in _CHECKER exists in EMPLOYEE?

 

Thanks to all!

 

Frank

 

Labels (2)
2 Replies
ogster1974
Partner - Master II
Partner - Master II

This will get you what you need

_Checker:
load upper(CHECKER) as CHECKER Inline [
CHECKER
Tom
John
Lisa
];

Mapping
EmpChkMap:
Load
CHECKER,
1 as Exists
Resident _Checker
;

Drop table _Checker;

_EMPLOYEE:
load Upper(EMPLOYEE) as EMPLOYEE inline [
EMPLOYEE
'Tom Smith'
' Marie Lisa Walter'
'Will Smith'
];

NoConcatenate
EmployeeChecker:
Load
*,
ApplyMap('EmpChkMap',Name,0) as NameExists
;
Load
EMPLOYEE,
SubField(EMPLOYEE,' ') as Name
Resident _EMPLOYEE
;

ogster1974_0-1674551231397.png

 

sidhiq91
Specialist II
Specialist II

@FrankGrimm  Please use the below script in your load Editor.

NoConcatenate
Checker:
load upper(CHECKER) as CHECKER Inline [
CHECKER

Tom

John

Lisa
];

NoConcatenate
EMPLOYEE_temp:

load Upper(SubField(EMPLOYEE,' ',1)) as First_Name,
Upper(SubField(EMPLOYEE,' ',2)) as Middle_Name,
Upper(SubField(EMPLOYEE,' ',3)) as Last_Name
inline [

EMPLOYEE

'Tom Smith'

'Marie Lisa Walter'

'Will Smith'

];

NoConcatenate
EMPLOYEE:
Load *
Resident EMPLOYEE_temp
where exists(CHECKER,First_Name) or exists(CHECKER,Middle_Name) or exists(CHECKER,Last_Name);

Drop table EMPLOYEE_temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

sidhiq91_0-1674556858808.png