How to use Nested CASE statements in looker using case | Example of Nested Case Statement in Looker
we can use Nested CASE statements to create more complex "branching" logic:
For example here the sales target for category1 and category 2 is different,
if the sales is below 350 then outcome will be poor
if the sales is above 350 and below 700 then outcome will be good
if the sales is above 700 then outcome will be excellent
Then for sales target for category3,category4 and category 5 its different,
if the sales is below 200 then outcome will be poor
if the sales is above 200 and below 500 then outcome will be good
if the sales is above 500 then outcome will be excellent
So in this video we will create this a table and then we are going to apply or use the Nested CASE statements in calculated field in our charts or tables from scratch so that you can get the awareness that when where and how we can apply the Nested CASE statementsin our looker report or dashboard
And before we use this Nested CASE statements, let's understand the Nested CASE statements first.
So Nested CASE statements is a condition statement , which Evaluates a list of conditions and returns the first matching result expression.
CASE defines one or more conditions and returns a result when a condition is met. You can define a default result if none of the conditions is met.
Now lets go and create a calculated field using Nested CASE statementsin our table
– process–
Syntax ➖
CASE
WHEN condition THEN result
case
[WHEN condition THEN result]
[...]
[ELSE else_result]
End
Else
END
Parameters
WHEN condition
WHEN conditions evaluate your data and return true if the specified condition is met, or false if it isn't. You can use any valid Boolean expression as the WHEN conditions.
THEN result
Each WHEN condition must have a matching THEN clause, which specifies the results if that condition is true. If there are multiple WHEN conditions, the CASE expression returns the result for the first true condition.
ELSE else_result
The optional ELSE clause specifies a default result. This is returned if none of the WHEN clauses are true. If a CASE expression has no ELSE clause, and none of the WHEN clauses are true, the statement returns NULL. A CASE expression can only have 1 ELSE clause.
When you are using case statement you have to Note that :-
All of the possible results in a THEN clause must be of the same type.
For example, if the first THEN clause returns Text, additional THEN clauses, as well as the ELSE clause must also return Text.
Case
WHEN Category in ("Category-1", "Category-2" ) then
Case
when Qty Sold less than 350 then "Poor"
when Qty Sold greater than equal to 350 and Qty Sold less than 700 then "Good"
when Qty Sold greater than equal to 700 then "Excellent"
End
WHEN Category in ("Category-3", "Category-4", "Category-5" ) then
Case
when Qty Sold less than 200 then "Poor"
when Qty Sold greater than equal to 200 and Qty Sold less than 500 then "Good"
when Qty Sold greater than equal to 500 then "Excellent"
End
End
As we have successfully applied and use the Nested CASE statements in our calculated field to create a new dimension you might have a question in your mind that How does this Nested CASE statementsworks
So A CASE expression begins with the CASE keyword and ends with the END keyword. In between, you'll have a number sections or "clauses":
WHEN: a condition you want to evaluate. You can have multiple WHEN clauses in a single CASE expression.
THEN: the result to return if the WHEN clause's condition is true. You must have one THEN clause for each WHEN clause in your CASE expression.
ELSE. Optional. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause, or NULL if no ELSE clause is specified.
CASE evaluates each successive WHEN clause and returns the first result where the condition is true. Any remaining WHEN clauses and the ELSE result are not evaluated. If all WHEN conditions are false or NULL, CASE returns the ELSE result, or if no ELSE clause is present, returns NULL.
You can follow us on the below social media handles:-
Youtube: - / @thedatamillennials
Facebook:- / the-data-millennials-596575371027542
Instagram: - / the_data_millennials
LinkedIn: - / data-millennials-6b5a031b3
Twitter: - / datamillennials
If you want to be become an Data Analyst or BuCASEess Analyst who has a great analytical Skill then you can follow the below links
Excel Playlist:-https://bit.ly/3rd9cEH
SQL Playlist:- https://bit.ly/3v28GKY
Google Data Studio playlist:- https://bit.ly/3v7czyc