How to exclude BLANK values in histogram custom bins?
My beast mode calculation is the following:
CASE
WHEN CEILING(`Flow Velocity (ft/s)`)<= 10 THEN '0-10'
WHEN CEILING(`Flow Velocity (ft/s)`)<= 50 THEN '10-50'
WHEN CEILING(`Flow Velocity (ft/s)`)<= 100 THEN '50-100'
WHEN CEILING(`Flow Velocity (ft/s)`)<= 150 THEN '100-150'
ELSE '150+'
END
I have realised that any BLANK values in that column are being bundled into 150+ bucket. I can't seem to filter out any BLANK values through the filter function on the card. Is there a way I can remove these BLANK values so they are not counted at this specific card level?
Thanks
Best Answer
-
Hi @user000253
Are your values blank meaning an empty string '' or are they actually NULL meaning no data?
To handle both cases you could add an additional clause to your case statement with a COALESCE function:
CASE WHEN TRIM(COALESCE(`Flow Velocity (ft/s)`, '')) = '' THEN 'BLANK' WHEN CEILING(`Flow Velocity (ft/s)`)<= 10 THEN '0-10' WHEN CEILING(`Flow Velocity (ft/s)`)<= 50 THEN '10-50' WHEN CEILING(`Flow Velocity (ft/s)`)<= 100 THEN '50-100' WHEN CEILING(`Flow Velocity (ft/s)`)<= 150 THEN '100-150' ELSE '150+' END
COALESCE says to return the first non-null value it finds. So in this case if it's an empty string ('') it will return that value and compare it to an empty string, if it's null it will use the empty string to compare against instead of null. There's an extra TRIM in there just in case we have a value of all spaces to remove spaces from the beginning and end of our string (it would make a value of ' ' = '')
And then just filter out the BLANK value in your card.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
Hi @user000253
Are your values blank meaning an empty string '' or are they actually NULL meaning no data?
To handle both cases you could add an additional clause to your case statement with a COALESCE function:
CASE WHEN TRIM(COALESCE(`Flow Velocity (ft/s)`, '')) = '' THEN 'BLANK' WHEN CEILING(`Flow Velocity (ft/s)`)<= 10 THEN '0-10' WHEN CEILING(`Flow Velocity (ft/s)`)<= 50 THEN '10-50' WHEN CEILING(`Flow Velocity (ft/s)`)<= 100 THEN '50-100' WHEN CEILING(`Flow Velocity (ft/s)`)<= 150 THEN '100-150' ELSE '150+' END
COALESCE says to return the first non-null value it finds. So in this case if it's an empty string ('') it will return that value and compare it to an empty string, if it's null it will use the empty string to compare against instead of null. There's an extra TRIM in there just in case we have a value of all spaces to remove spaces from the beginning and end of our string (it would make a value of ' ' = '')
And then just filter out the BLANK value in your card.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Categories
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive