CASE WHEN X IS NULL THEN Previous Value

Reply
Major Blue Belt

CASE WHEN X IS NULL THEN Previous Value

Tried ETL Rank & Window and MySQL, but just can't crack this. If my field is null, then give me the previous value. 

AccountDate Cost 
120594815-Aug-2019 $  42,246.48
120594816-Aug-2019 
120594817-Aug-2019 
120594818-Aug-2019 
120594819-Aug-2019 $  42,246.48
120594820-Aug-2019 $  20,022.44
120594821-Aug-2019 
120594822-Aug-2019 
120594823-Aug-2019 $  46,556.96
Original  
 Desired 
AccountDate Cost 
120594815-Aug-2019 $  42,246.48
120594816-Aug-2019 $  42,246.48
120594817-Aug-2019 $  42,246.48
120594818-Aug-2019 $  42,246.48
120594819-Aug-2019 $  42,246.48
120594820-Aug-2019 $  20,022.44
120594821-Aug-2019 $  20,022.44
120594822-Aug-2019 $  20,022.44
120594823-Aug-2019 $  46,556.96
Major Brown Belt

Re: CASE WHEN X IS NULL THEN Previous Value

Yellow Belt

Re: CASE WHEN X IS NULL THEN Previous Value

I am not sure how the performance would be like on a large dataset, but on a sample data, it can be achieved in 2 steps.

 

Step 1 - Create a transformation call rank_table

SELECT `Account`,`Date`,`Cost`, @curRank := @curRank + 1 AS rank
FROM `blank_row`, (SELECT @curRank := 0) tmp
ORDER BY `Account`, `Date`

 

Step2 : Apply the following sql in theoutput_dataset

select a.*, COALESCE(a.`Cost`, (select (cost) from rank_table where rank < a.`rank` and `Cost` >0 order by rank DESC limit 1) ) as BackFillCost
from rank_table as a

 

Backfill.png

Major Blue Belt

Re: CASE WHEN X IS NULL THEN Previous Value

managed to figure it out with some joins on the dates that also brough forward the previous values. I tried ever variation i could find with PREV, and @prevValue, etc but nothing would work. all I got were errors. Any idea what version of MySQL DOMO is using? 

Highlighted
White Belt

Re: CASE WHEN X IS NULL THEN Previous Value

version.png

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!