Set expression that matches multiple strings values with separate list of strings
I have a set expression that searches for a single product name in a list of product names:
=if(match(product_number, {<isEUCountry={1}>}product_number)=1, 'Export possible', 'Export not possible')
However, one product can have multiple ingredients and match only takes one string in its first parameter:
Syntax: match( str, expr1 [ , expr2,...exprN ])
I want to do a similar search for ingredients in a list of ingredients, but I haven't found a function in Qlik that will take multiple strings in the first parameter:
=if([function_name](list_of_ingredients, {<isEUCountry={1}>}list_of_ingredients)=1, 'Export possible', 'Export not possible')
One alternative solution could be to loop the match function for each string in my list of ingredients, but not sure if that is possible.
I have a set expression that searches for a single product name in a list of product names:
=if(match(product_number, {<isEUCountry={1}>}product_number)=1, 'Export possible', 'Export not possible')
However, one product can have multiple ingredients and match only takes one string in its first parameter: my spotify pie
Syntax: match( str, expr1 [ , expr2,...exprN ])
I want to do a similar search for ingredients in a list of ingredients, but I haven't found a function in Qlik that will take multiple strings in the first parameter:
=if([function_name](list_of_ingredients, {<isEUCountry={1}>}list_of_ingredients)=1, 'Export possible', 'Export not possible')
One alternative solution could be to loop the match function for each string in my list of ingredients, but not sure if that is possible.
Hello,
To achieve this in Qlik, you can use a combination of SubField and Match functions to handle multiple strings in your list of ingredients. Here’s a step-by-step approach:
Split the Ingredients: Use the SubField function to split the list of ingredients into individual ingredients. Match Each Ingredient: Use the Match function to check if each ingredient is in the list of ingredients for EU countries. Aggregate the Results: Use an aggregation function like Max or Sum to determine if any of the ingredients match. Here’s an example of how you can implement this:
// Load your data LOAD product_number, list_of_ingredients, isEUCountry FROM your_data_source;
// Create a flag for export possibility LOAD product_number, list_of_ingredients, isEUCountry, If(Max(Match(SubField(list_of_ingredients, ',', IterNo()), {<isEUCountry={1}>}list_of_ingredients)) > 0, 'Export possible', 'Export not possible') as ExportFlag Resident your_data_source While IterNo() <= SubStringCount(list_of_ingredients, ',') + 1;
Explanation: SubField: This function splits list_of_ingredients into individual ingredients based on the comma delimiter. Match: This function checks if each ingredient is in the list of ingredients for EU countries. Max: This function aggregates the results to determine if any of the ingredients match. This approach ensures that you can handle multiple ingredients and check if any of them are in the list of ingredients for EU countries.
