Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
_nappi
Contributor II
Contributor II

Find value in a list

Hi all,

i have two list of clients, list A and list B. 

I need to find a logic to create a flag that tells me: if all values of list A are present in list B, "total match", if at least one value of list A is in list B, "partial match", if no value of list A is in list B", "no match".

 

Thank you

Labels (5)
4 Replies
sandeep-singh
Creator II
Creator II

You can try below script. If still encounter any issues, then share some dummy sample to validate it.

if(match(List_A,List_B),'Total Match'
,if(LevenshteinDist (List_A,List_B),'Partial Match'
,'No Match'))

_nappi
Contributor II
Contributor II
Author

Hi @sandeep-singh , thank you so much for your solution but it didn't work because "no match" classification doesn't find value. I tried in this way:

 

if(match(List_A,List_B),'Total match',

if(wildmatch(List_A,List_B),'Partial',

'no'))

 

But also in this way doesn't work. Are there other solution?

Thanks so much

sidhiq91
Specialist II
Specialist II

@_nappi  Please try the below code in the script Editor.

NoConcatenate
Temp:
Load Count(distinct Name1) as Total_Count

inline [
Name1
Sidhiq
Satish
Avinash
Lokesh
];

Let VMax_Count=Peek('Total_Count',0,'Temp');

Trace >>>Total Count is $(VMax_Count);

Drop table Temp;

NoConcatenate
Temp1:
load * inline [
Name1
Sidhiq
Satish
Avinash
Lokesh
];


NoConcatenate
Temp2:

Load
if(count(distinct Name2)='$(VMax_Count)', 'total_Match',
if(count(distinct Name2)<'$(VMax_Count)','Partial_match','No_Match')) as Flag

inline [
Name2
Sidhiq
Naveen
Bharath
Madhu
]
where exists (Name1,Name2);

Exit Script;

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

aveeeeeee7en
Specialist III
Specialist III

Hi @_nappi,

@sandeep-singh shared the right approach.

You need to make few tweaks in his proposed solution as per the results.

Try this:

=IF(MATCH(CONCAT(chr(39)&List_A&chr(39),', '),CONCAT(chr(39)&List_B&chr(39),', ')),'Total Match',
IF(LEVENSHTEINDIST(CONCAT(chr(39)&List_A&chr(39),', '),CONCAT(chr(39)&List_B&chr(39),', ')),'Partial Match','No Match'))

Regards,

Av7eN