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
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 384 Distribute
- 110 Domo Everywhere
- 268 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive