Hello,
We have, in several lookerviews, CASE WHEN functions that translates a fixed inputvalue to a specific outputvalue.
Is it possible to create this as a predefined function (or something similiar) in looker, and import it in the necessary views ? it would give us the ability to only change that function in one place and the edit would be applied everywhere.
as of now I have to edit every related view manually, and the risk of errors is pretty high.
Looks like a great use case for a constant!
Check out this page:
LookML parameter: Define a constant to use throughout a project.
Hi Cyril, and Thanks for the reply.
I checked out the documentation, but didn’t find anything regarding CASE WHEN specifically, or anything more advanced than single constants.
have you tried out the constants before?
regards
Alex
So, you can use any string in the constant value including your CASE WHEN
constant: CASE_WHEN_FILTER {
value: "
CASE
WHEN ${id} = \"xxx\" THEN \"aa\"
WHEN ${id} = \"yyy\" THEN \"ab\"
WHEN ${id} = \"zzz\" THEN \"ac\"
ELSE \"-\"
END
"
then just refer to your constant in the sql
parameter of your dimension like so:
dimension: id_clean {
type: string
sql: @{CASE_WHEN_FILTER} ;;
}
I tried this out, and it worked like a charm! thanks for the help!
Constants is a good solution! I also wanted to share another approach with different tradeoffs/usecases - using lateral joins
Here’s an example in BQ syntax
view: calculated_column {
sql_table_name: UNNEST([
CASE WHEN ${foo.col} = "a" THEN ... END
]) ;;
dimension: value {
sql: ${TABLE} ;;
}
}
explore: foo{
join: calculated_column {
view_label: "Foo"
type: cross
relationship: one_to_one
}
}
That’s quite innovative!
Could you elaborate on the benefits of that approach?
It just came to mind because I’ve used the pattern before as a way to reduce repetitive blocks of CASE WHENs within the SQL query itself. For example, I’ve sometimes seen nested CASE WHEN’s generated by using the ${} operator that caused the SQL query to be over 1MB. I guess it’s not exactly the case here, but wanted to mention it anyway!
I have a follow-up question on this thread.
I have a pretty similar but different use case. I want to apply the same CASE WHEN statement to different fields:
A mock example of my use case for illustration:
I have 3 columns with country id: client_country, supplier_country, user_country.
I want to replace country ids by country names with a CASE statements for this 3 columns without replicating the code everywhere.
I think that constant (as described above) does not work for my use case as I would have to put one of three columns name in the constant statement. For ex, if I put client_country in constant statement, it won’t apply to supplier_country and user_country.
Note that my real use case has 40 columns so I really need something scalable 🙂
Any ideas?