Import Predefined CASE WHEN statements into views

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.

image

0 8 485
8 REPLIES 8

Looks like a great use case for a constant!

Check out this page:

constant

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?

Top Labels in this Space
Top Solution Authors