Workbench transform for NULL from SQL data
Comments
-
0
-
Hi @gaurav007,
There are a couple options to detect/replace NULL values in your data.
- You can perform the operation in Workbench before the data gets loaded to Domo. In the ODBC query window you can use the SQL function native to your source system. i.e for. MSSQL use "ISNULL(your_column,0)", for PL/SQL use "NVL(your_column,0)", for MySQL use "IFNULL(your_column,0)", etc... If your SQL platform is not one of these three then please let me know which platform you are using and I will provide the correct function.
- If the data has already been loaded to your Domo data center then you can use a MySQL dataflow transform to replace NULL with 0. Again, that MySQL function is "IFNULL(your_column,0)"
Please let me know if that does not answer your question.
Thank you,
Creed
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
@gaurav007, did creed's reply help you out?
0 -
creed's answer is great. If you can do this in the SQL statement that will be the best and fastest way to get this done. I wanted to offer one other option just for the sake of completeness. In workbench (the current version of Wb4) you can do this with a calculated field or a search and replace transform.
A calculated field adds a new column based off the original, and you can use a formula like: IF(ISNULL(Column),0,Column)
For the search and replace, you can search for \x00 (which is a special regular expression workbench uses to represent the null value), and replace with 0.
2 -
This is really helpful - thank you!
0 -
Hi @jeremyhurren,
For the search and replace function, do you know how find blank string and replace them with nulls?0
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