Set expression that matches multiple strings values with separate list of strings

prayner — Mon, 26 Aug 2024 14:12:29 GMT

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.

Re: Set expression that matches multiple strings values with separate list of strings

samuel898 — Tue, 03 Sep 2024 03:58:31 GMT

@prayner wrote:

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.

Hope this will help you.
Best regards,
samuel898

topic Set expression that matches multiple strings values with separate list of strings in App Development

Set expression that matches multiple strings values with separate list of strings

Re: Set expression that matches multiple strings values with separate list of strings