Filter is removing nulls plus specified value
Hi All,
Question:
I am using a Magic ETL filter with:
- level = 2
- status = 'Blue'
The filter is removing 'Null' values in the status field as well as the Blues. What's the potential cause here?
Context:
Both fields are string dtypes (abc)
I've tried:
- level = 2 AND status <> blue -- in one formula
- status NOT IN('Blue')
- Creating another filter transformation -- same issue
All responses appreciated
Kind regards,
Answers
-
@NateBI Nulls don't play well with logical operators. Null isn't the same thing as nothing, it really means that the value is unknown. Is null blue? SQL doesn't know so it treats it as if it meets the condition.
You can use a formula rule to get around this:
IFNULL('status','') = 'Blue'
This will replace your NULL value with a blank which it can evaluate in the comparison.
Alternatively, you could use the formula:
'status' = 'Blue' or 'status' IS NULL
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
2 -
Hi @RobSomers thank you for this -- this concept worked I just adapted it to filter out the Blues but keep NULLs with :
IFNULL(`status`<>'Blue',TRUE)
Thanks!
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 761 Beast Mode
- 65 App Studio
- 42 Variables
- 703 Automate
- 182 Apps
- 458 APIs & Domo Developer
- 53 Workflows
- 10 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 132 Manage
- 129 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive