Beast mode CASE calculation for number ranges that have null values in column.
I have a column that has data type numeric and has numeric values and around 5 blank values.
I am trying to categorize the number to ranges 1-5, 5-10 and so on. The column has blank values as well. Well, when I am trying to implement the CASE calculation in beast mode, it throws me a calculation error. How do I correct this?. How do I handle " blank values" is it affecting my beast mode calculation?
Best Answers
-
Since
CASE
is always checked in order, you can simplify your beast mode to something like:CASE WHEN COALESCE(`New_Column_Name`,0) = 0 THEN 'a) Zero or Blank'
WHEN `New_Column_Name` < 5 THEN 'b) Less than 5' WHEN `New_Column_Name` <= 10 THEN 'c) 5-10' WHEN `New_Column_Name` <= 20 THEN 'd) 11-20' WHEN `New_Column_Name` <= 30 THEN 'e) 21-30' WHEN `New_Column_Name` <= 50 THEN 'f) 31-50' WHEN `New_Column_Name` <= 100 THEN 'g) 51-100' ELSE 'h) More than 100'
ENDThe first one will take care of the blank values, keep in mind that this will work as long as your column is in fact a number column and not text. Let us know if this helps.
The reason I added letters is so that if you graph and you want to make them display in the given order you need to either do this, or have a separate beast mode returning an index to sort by (which won't work for Tables and Pivot Tables).
0 -
@art_in_sky Try.
CASE
WHENNew_Column_Name
< 5 THEN 'Less than 5'
WHENNew_Column_Name
>= 5 ANDNew_Column_Name
<= 10 THEN '5-10'
WHENNew_Column_Name
>= 11 ANDNew_Column_Name
<= 20 THEN '11-20'
WHENNew_Column_Name
>= 21 ANDNew_Column_Name
<= 30 THEN '21-30'
WHENNew_Column_Name
>= 31 ANDNew_Column_Name
<= 50 THEN '31-50'
WHENNew_Column_Name
>= 51 ANDNew_Column_Name
<= 100 THEN '51-100' ELSE 'More than 100'
END0 -
The separation for Null should work, you can do it in several different ways, so your first statement would be:
CASE
WHEN `New_Column_Name` IS NULL THEN 'a) Blank'
…And from there you cover all the other scenarios. Since case is always checked in order and stops after the first condition met is fulfilled, for example if you have a value of 47, it'll see see that it doesn't fulfill any condition until it reaches the
< = 50
at which point it'll stop and return30-50
as the output, not sure what you mean by duplicate values. Personally I don't trust BETWEEN as it has never really worked for me.1 -
@art_in_sky AVG works based on the number of non null entries, so if you null out your zeros that will solve for it. A beast mode like:
CASE WHEN `Column` = 0 THEN NULL ELSE `Column` END
Which then you'll select to aggregate using AVG should do the trick or removing the 0s from your AVG calculation (or you can aggregate at the Beast Mode level should you want)
1
Answers
-
@art_in_sky can you post your beastmode please?
0 -
Please find my beast mode calculation below
CASE
WHEN `New_Column_Name` < 5 THEN 'Less than 5'
WHEN `New_Column_Name` BETWEEN 5 AND 10 THEN '5-10'
WHEN `New_Column_Name` BETWEEN 11 AND 20 THEN '11-20'
WHEN `New_Column_Name` BETWEEN 21 AND 30 THEN '21-30'
WHEN `New_Column_Name` BETWEEN 31 AND 50 THEN '31-50'
WHEN `New_Column_Name` BETWEEN 51 AND 100 THEN '51-100' ELSE 'More than 100'
END0 -
Since
CASE
is always checked in order, you can simplify your beast mode to something like:CASE WHEN COALESCE(`New_Column_Name`,0) = 0 THEN 'a) Zero or Blank'
WHEN `New_Column_Name` < 5 THEN 'b) Less than 5' WHEN `New_Column_Name` <= 10 THEN 'c) 5-10' WHEN `New_Column_Name` <= 20 THEN 'd) 11-20' WHEN `New_Column_Name` <= 30 THEN 'e) 21-30' WHEN `New_Column_Name` <= 50 THEN 'f) 31-50' WHEN `New_Column_Name` <= 100 THEN 'g) 51-100' ELSE 'h) More than 100'
ENDThe first one will take care of the blank values, keep in mind that this will work as long as your column is in fact a number column and not text. Let us know if this helps.
The reason I added letters is so that if you graph and you want to make them display in the given order you need to either do this, or have a separate beast mode returning an index to sort by (which won't work for Tables and Pivot Tables).
0 -
@art_in_sky Try.
CASE
WHENNew_Column_Name
< 5 THEN 'Less than 5'
WHENNew_Column_Name
>= 5 ANDNew_Column_Name
<= 10 THEN '5-10'
WHENNew_Column_Name
>= 11 ANDNew_Column_Name
<= 20 THEN '11-20'
WHENNew_Column_Name
>= 21 ANDNew_Column_Name
<= 30 THEN '21-30'
WHENNew_Column_Name
>= 31 ANDNew_Column_Name
<= 50 THEN '31-50'
WHENNew_Column_Name
>= 51 ANDNew_Column_Name
<= 100 THEN '51-100' ELSE 'More than 100'
END0 -
Thank you Marcel and Jones for your answers. Both of the queries worked for me . I am curious to know why my CASE statement with BETWEEN didn't work and I believe they are in order, Aren't they?
0 -
@marcel I think the less than operator kind of falsely categorizes the range values and include all possible duplicate values when indicated as less than equal to for each range. Do you Agree?
0 -
Also what if I want to specify the blank and zero values as separate categories?
0 -
2
-
Updating the beast mode gives me " issue request and doesnt display the graph", even though it says updated, when I go back to the beast mode calculation, it displays the old case statement.
Any inputs?0 -
The separation for Null should work, you can do it in several different ways, so your first statement would be:
CASE
WHEN `New_Column_Name` IS NULL THEN 'a) Blank'
…And from there you cover all the other scenarios. Since case is always checked in order and stops after the first condition met is fulfilled, for example if you have a value of 47, it'll see see that it doesn't fulfill any condition until it reaches the
< = 50
at which point it'll stop and return30-50
as the output, not sure what you mean by duplicate values. Personally I don't trust BETWEEN as it has never really worked for me.1 -
Thank you for your response. It helped.
0 -
I have zeroes in a column which are leading to miscalculation of average. I want to create a beast mode calcualtion to avoid the zeroes in my calcualtion and calculate only the rows that have actual values other than zeroes. How do I achieve this?
0 -
@art_in_sky AVG works based on the number of non null entries, so if you null out your zeros that will solve for it. A beast mode like:
CASE WHEN `Column` = 0 THEN NULL ELSE `Column` END
Which then you'll select to aggregate using AVG should do the trick or removing the 0s from your AVG calculation (or you can aggregate at the Beast Mode level should you want)
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive