Using pass-through functions to identify if parameter selections are listed in string column

Featured How-To Articles
1

Say you’ve got a multi-select parameter that lists some number of skills/tags, and a table which has a string column that has different skills/tags listed (delimited by commas and spaces) per row:

image
image1790×1602 118 KB

Now, say you’d like to do 2 things:

  1. Identify which rows contain at least one of the skills/tags selected in the “Tag Select” parameter.
  2. Identify which rows contain all selected skills/tags in the “Tag Select” parameter.

Since you’ll need to iterate through arrays to accomplish the aforementioned, you’ll need to use some Sigma pass-through functions in conjunction with database-specific functions. Please note that the following steps will only work for those on Snowflake… if you’re not on Snowflake, you’ll need to find alternative functions to employ for this challenge.

Steps

  1. Create a new column called [Tags as Array] using the formula:
    • CallVariant("strtok_to_array", RegexpReplace([Tags], ", ", ","), ",")
  2. Create a new column called [Common Tags] using the formula:
    • CallVariant("array_intersection", [Tags as Array], [tag-select-parameter])
  3. To solve the first part of the challenge, create a new column called [Any Tag in Selection] using the formula:
    • CallNumber("array_size", [Common Tags]) > 0
  4. To solve the second part of the challenge, create a new column called [All Selections in Tags] using the formula:
    • CallNumber("array_size", [tag-select-parameter]) = CallNumber("array_size", [Common Tags])
  5. Hide any columns you don’t want to see.
3 Likes