How can I replace the empty values from one column with the unique value from a different column?

user03757
user03757 Member
edited March 2023 in Datasets

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

  • emendoza
    emendoza Domo Employee
    Answer ✓

    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"

  • user03757
    user03757 Member
    Answer ✓

    Perfect!  That worked  -  Thank you so much!

Answers

  • Chronos
    Chronos Member

    Is anyone able to help out with this request?

  • emendoza
    emendoza Domo Employee

    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"

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • user03757
    user03757 Member

    Thank you for trying - unfortunately this is still not pulling the estimated with updated value is 0    - See attached

     

     

  • emendoza
    emendoza Domo Employee
    Answer ✓

    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"

  • user03757
    user03757 Member
    Answer ✓

    Perfect!  That worked  -  Thank you so much!

  • user13436
    user13436 Member

    Can you do this without using SQL? 

Categories