How can I replace the empty values from one column with the unique value from a different column?
I have Estimated Budget hours (ie 800) and then sometimes we Update our hours (ie 3,600) to a new "Updated Budget" if needed. (This "update budget" is set up to include the original estimate so I cannot simply add them) I need a column to combine using the Updated Budget hours except when ' ' empty or 0, then use the hours in the Estimated Budget.
I do know the Updated Budget is ' ' (empty) See attachment
I have tried this:
SELECT processimprovement_dwb. *,
Case WHEN `Updated Budget` like ' ' then (`Estimated Budget`+`Updated Budget`)
else `Updated Budget`
end as 'Estimated and Updated Hours' from processimprovement_dwb
And I have tried this:
SELECT processimprovement_dwb. *,
Case WHEN `Updated Budget` like '' then (`Estimated Budget`+`Updated Budget`)
else `Estimated Budget`
end as 'Estimated and Updated Hours' from processimprovement_dwb
But neither one gives me the result I need.
I need to see the value for the Estimated Budget where there was a 0 for Updated Budget.
Please see attachment.
Best Answers
-
Hello,
Thank you for providing that screenshot! I see my mistake now. Try changing the 'OR's into 'AND's in the query I provided earlier and see if that works.
Enoc Mendoza
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Perfect! That worked - Thank you so much!
0
Answers
-
Is anyone able to help out with this request?
0 -
Hello,
If I understand correctly, essentially you want to check to see if "Updated Budget" is either ' ' empty or NULL, if so, use the current "Estimated Budget", but if is not NULL or empty, then use the "Updated Budget" is that correct? In this case, I'd use something like this:
SELECT
(CASE WHEN (`Updated Budget` IS NOT NULL) OR (`Updated Budget` != '') OR (`Updated Budget` != 0)THEN `Updated Budget`
ELSE `Estimated Budget`
END) AS 'Estimated and Updated Hours'
FROM 'table_name'
This will cause Update Budget to always be used if it has something in it that isn't empty/NULL/0, and otherwise it will use the Estimated Budget. Does that make sense? Let me know if that works for you!Enoc Mendoza
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Have you tried this:
SELECT
ifnull(`Updated Budget`,`Estimated Budget`) as `Estimated and Updated Hours`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Sorry, it may be too early to help on Dojo for me this morning. I read your post a little more closely. I think that I would approach this like this:
SELECT *,
CASE(WHEN `Updated Budget` != 0 THEN `Estimated Budget` ELSE `Updated Budget` END AS `Estimated and Updated Hours`,
FROM (SELECT `Estimated Budged`,
ifnull(`Updated Budget`,0) AS `Updated Budget` -- This will enter a 0 any time the updated budget is null
FROM processimprovement_dwb) a
This statement will make a temporary table that replaces any null values from the Updated Budget field with 0's first.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Thank you for trying - unfortunately this is still not pulling the estimated with updated value is 0 - See attached
0 -
Hello,
Thank you for providing that screenshot! I see my mistake now. Try changing the 'OR's into 'AND's in the query I provided earlier and see if that works.
Enoc Mendoza
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Perfect! That worked - Thank you so much!
0 -
Can you do this without using SQL?
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 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
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive