Let’s say you have a Text type column of identification numbers, like the [Order Numbers] column below:
You’d like to filter this column with exact matches from a list of values. The length of your list is arbitrary so using a List filter to hand select the values wouldn’t be effective.
We can achieve this using a series of pass-through functions to take advantage of warehouse functions that are able to compare the contents of Arrays. In the example formula below, we’re using Snowflake’s warehouse functions but other CDWs will more than likely have their own equivalents.
CallLogical("array_contains", CallVariant("to_variant", Text([Order Number])), CallVariant("strtok_to_array", "559452,559466,557579", ","))
The list of values you provide as the first argument for the “strtok_to_array” function needs to be separated by the delimiter provided in the second argument. In the example above, we’re using a comma as the delimiter so our list of values is a single string with the values within separated only by a comma.
We can make a new column to run this formula in, the result of which will be a Boolean for whether the given values in the list are detected:
You can then filter that column to only show True values and hide it, as needed:
You don’t have to hard code in the list of values you want to filter by inside that formula. You can let users input their own values using a Textbox parameter! (Users must just be aware to separate the values by the delimiter chosen as the second argument of the “strtok_to_array” function).
Lastly, it’s possible to pass a hard coded list of values, or a list from a Textbox parameter, into CustomSQL so that the filtration occurs in there! You will call the same warehouse functions as above.
select ORDER_NUMBER
from EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
where
case when {{prm_Order_Num}} is null
then True
else array_contains(ORDER_NUMBER::text::variant, strtok_to_array({{prm_Order_Num}}, ','))
end
Note: if you want to reproduce this how-to, you can find the same [Order Numbers] column inside the “PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA” table within our Sigma Sample Connection.