How to Evaluate logical AND/OR condition in looker using case | Case Statement with logical Operator
Вставка
- Опубліковано 6 вер 2024
- So in this video we will create this a table and then we are going to apply or use the CASE with logical AND/OR condition 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 CASE with logical AND/OR condition in our looker report or dashboard
And before we use this function, let's understand the CASE first.
So CASE 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 CASE in our table
- process-
Syntax ➖
CASE
WHEN condition THEN result
[WHEN condition THEN result]
[...]
[ELSE else_result]
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="Category-1" OR Category="Category-2" AND Unit Price greater than equal to 75 THEN "Europe"
WHEN Category="Category-3" OR Category="Category-2" OR Category="Category-5" AND Unit Price greater than equal to 75 THEN "America"
Else "Asia"
END
As we have successfully applied and use the CASE in our calculated field to create a new dimension you might have a question in your mind that How does this CASE works
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:-
UA-cam: - / @thedatamillennials
Facebook:- / the-data-millennials-5...
Instagram: - / the_data_millennials
LinkedIn: - / data-millennials-6b5a0...
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:-bit.ly/3rd9cEH
SQL Playlist:- bit.ly/3v28GKY
Google Data Studio playlist:- bit.ly/3v7czyc