Case Statement In Looker

In Looker, a case statement is used to create a new dimension or measure that is based on the values of another dimension or measure. It allows you to create a new field that has different values depending on the values of another field. The basic syntax for a case statement in LookML is as follows:

case
  when [dimension or measure] = [value] then [output value]
  when [dimension or measure] = [value] then [output value]
  ...
  else [output value]
end

For example, you might use a case statement to create a new dimension called “status” that is based on the values of a dimension called “status_code”. You might define the new dimension as follows:

dimension: status {
  type: string
  case:
    when status_code = 1 then "active"
    when status_code = 2 then "inactive"
    when status_code = 3 then "deleted"
    else "unknown"
  end
}

This will create a new dimension called “status” that has the values “active”, “inactive”, “deleted” or “unknown” depending on the value of the “status_code” dimension.

What is Looker BI?

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

In a looker case statement, you can specify one or more when clauses, each of which tests whether a certain condition is true. The first when clause that is true will have its corresponding then value returned. If none of the conditions are true, the else value will be returned.

For example, if you wanted to create a new dimension called “status” based on the values of a dimension called “status_code”. You could use a case statement like this:

dimension: status {
  type: string
  case:
    when status_code = 1 then "active"
    when status_code = 2 then "inactive"
    when status_code = 3 then "deleted"
    else "unknown"
  end
}

In this example, the dimension “status” will have the values “active”, “inactive”, “deleted” or “unknown” depending on the value of the “status_code” dimension.

Another example would be for measure where you want to apply different calculations based on the value of another dimension

measure: total_price {
  type: sum
  sql: ${price}
  case: 
    when category = 'book' then ${price} * 1.2
    else ${price}
  end
}

This example a measure called “total_price” that is calculated as the sum of the “price” field, but for books it will be multiplied by 1.2.

You can also use case statements in conjunction with other functions and aggregations in LookML. This allows you to create more complex and powerful calculations and dimensions.

Use Cases

A common use case for using case statements in LookML is to create a new dimension or measure based on multiple conditions. For example, you may have a dimension called “age” and you want to create a new dimension called “age_group” that categorizes the age into different groups like “kids”,”teen”,”adult”, “senior citizen”. You could use a case statement like this:

dimension: age_group {
  type: string
  case:
    when age < 18 then 'kids'
    when age >= 18 and age <= 25 then 'teen'
    when age > 25 and age <= 60 then 'adult'
    when age > 60 then 'senior citizen'
    else 'unknown'
  end
}

This will create a new dimension called “age_group” that has the values “kids”, “teen”, “adult”, “senior citizen” or “unknown” depending on the value of the “age” dimension.

You can also use case statement in combination of other functions and operators, like coalesce, ifnull, is_null or nullif to handle null values or missing data. You can also use Looker’s lookup function in conjunction with a case statement to perform more complex data transformation.

Here is an example that uses a lookup function

dimension: region {
  type: string
  case:
    when postal_code in lookup('zip_to_region', 'zip_code', 'region') then zip_code
    else 'unknown'
  end
}

This will create a new dimension called “region” and will lookup in the ‘zip_to_region’ view, to map the postal_code to the corresponding region.

It’s also worth mentioning that you can use case statement in the SQL of the measure, allowing you to use the case statement directly on the database level. This can help improve the performance of your queries when working with large data sets.

Please let me know if you have any more question about using the case statement in Looker.

Looker Interview Questions And Answers

Reference

About Me:-
I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to me if you are interested to evaluate if Looker is right for you or any other BI solution.