Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
D19PAL
Creator II
Creator II

Check if value is in another field

Hi,

How do i check if a value from 1 field is in another?

ID,Name,Name2

1,john,james

2,james,paul

3,mark,sue

So only show the rows where theres a match of The value in Name2  and Name and then flag both rows, id 1 and 2 not 3.

 

Thanks 

 

 

 

Labels (1)
3 Replies
maxgro
MVP
MVP

try this, result in Match field

 

Test:
LOAD * INLINE [
ID,Name,Name2
1,john,james
2,james,paul
3,mark,sue
];

LEFT JOIN (Test)
LOAD DISTINCT
Name2 as Name,
1 as NameMatchName2
Resident Test;

LEFT JOIN (Test)
LOAD DISTINCT
Name as Name2,
1 as Name2MatchName
Resident Test;

LEFT JOIN (Test)
LOAD
ID,
IF(NameMatchName2 OR Name2MatchName, 1, 0) as Match
RESIDENT Test;

D19PAL
Creator II
Creator II
Author

Thanks,

What i'm trying to do is the following as the final outcome.

 

Hi,

How do i check if a value from 1 field is in another?

ID,Name,Name2

1,john,james

2,james,paul

3,mark,sue

So only show the rows where theres a match of The value in Name2  and Name and then flag both rows, id 1 and 2 not 3.

Thanks for your help 

 

maxgro
MVP
MVP

Test:
LOAD * INLINE [
ID,Name,Name2
1,john,james
2,james,paul
3,mark,sue
];

LEFT JOIN (Test)
LOAD DISTINCT
Name2 as Name,
1 as NameMatchName2
Resident Test;

LEFT JOIN (Test)
LOAD DISTINCT
Name as Name2,
1 as Name2MatchName
Resident Test;

LEFT JOIN (Test)
LOAD
ID,
IF(NameMatchName2 OR Name2MatchName, 1, 0) as Match
RESIDENT Test;

 

In a chart you can show the rows with a match

dimensions ID, Name, Name2

measure Max(Match)

and exclude the zero values

maxgro_0-1664279642777.png