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

Comments

  • 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

  • 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? 

  • @Sweep_The_Leg Hi! I have the same problem with my dataset. May I know how you did it?

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟣

    @nicangelica this one is a brain teaser.


    assume we are trying to capture the last time 'cost changed'


    1) window function calculate the previous cost (lag, 1)

    2) create a binary (isCostChanged) using a CASE statement.

    3) take the cumulative sum of isCostChange using window function (sum). at this point you should basically have a counter. in the initial example, rows 1-4 would be group 1, rows 5 would be group 2, row 6-8 would be group 3, rows 9 would be group 4.

    4) take the cumulative sum of cost partitioned by group. -- this will give you cost from the first row of each group.


    you can do this in SQL or in Magic ETL.

  • @jaeW_at_Onyx Hi Jae! Sorry for the late revert. This solved my problem, thanks! Also, just want to say your tips on youtube and dojo threads helped me on a lot of my ETL problems so thank you! 😀

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!