Using multiple values in a single parameter

  • 26 May 2021
  • 0 replies
  • 1491 views

Userlevel 2

  In the event that you want to do something crazy like this:

When you want parameters to be all they can be

 

Parameters are incredibly useful tools, which basically work by injecting values into your sql or labels or wherever you put them. They are usually marred by the fact that you can only use a single value at a time per parameter field.

However where we have SQL, there is a always a way to make things more complicated than they need to be, and more hacky than is feasible.

Behind the method to the madness (Written in SNOWFLAKE):

  parameter: number_list {
suggest_dimension: user_id
type: string
}

dimension: secondary_dimension {
type: string
sql: array_construct(
TO_NUMBER(SPLIT_PART({% parameter number_list %}, ',', 1)),
TO_NUMBER(
IFF(
SPLIT_PART({% parameter number_list %}, ',', 2) = '', 0, SPLIT_PART({% parameter number_list %}, ',', 2)))
)
;;
}
dimension: change_value {
type: string
sql: case
when ARRAY_CONTAINS(${user_id},${secondary_dimension}) then 'changed value'
else 'previous value'
end ;;
}
dimension: user_id {
type: number
sql: ${TABLE}."USER_ID" ;;
}

We first create our parameter to store the multiple values, written here as number_list as a type: string. This is so that we can use commas, or any other kind of delimiter to store our multiple values.

Then, we extract those multiple values in a dimension, and using the power of SQL we take the delimiter, a comma in our case, and use it to split our values into different parts of an array.
In this use case, we are comparing the values against a type: number dimension, so we casted the individual parts before putting them in the array with TO_NUMBER, although that will vary with your use-case.

From there its a simple ARRAY_CONTAINS in our third dimension to compare if the value for the current row exists inside of the array we created, and if it does, we do something (in this case we just change the outputted value).

Current issues with this setup are:
1. Its only setup to accept up to 2 values (we can add more but it is a bit of a manual process)
2. We are relying heavily on SQL, so there are dialect related drawbacks, and it can be difficult to convert it to another language.

I believe that both of the above issues can probably be overcome with some high-level liquid, but as a prototype this is functional.
Let me know in the comments if someone has a better way of doing this, or an idea for those liquid solutions!


0 replies

Be the first to reply!

Reply