CASE WHEN X IS NULL THEN Previous Value

Sweep_The_Leg
Sweep_The_Leg 🟡
in Dataflows

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

  • guitarhero23
    guitarhero23 🟢

    Probably going to have to dive into a MySQL dataflow.

     

    Here are some potential resources:

  • tadashii
    tadashii ⚪️

    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

  • Sweep_The_Leg
    Sweep_The_Leg 🟡

    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? 

  • Unknown
    Unknown

    version.png

  • nicangelica
    nicangelica ⚪️

    @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.

  • nicangelica
    nicangelica ⚪️

    @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! 😀

  • Sweep_The_Leg
    Sweep_The_Leg 🟡

    @nicangelica sorry i missed your reply. I'm not getting notifications when someone tags me.

    I managed to solve this with a number of MySQL and ETL's, but my solution takes hours to run. ending result was about 32M rows.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited May 3

    @Sweep_The_Leg

    For 32M rows (especially with Magic 2.0 I would expect this to take in the ballpark of 10 to 20 minutes.

    If you set it up with a Dataset View it should be virtually instantaneous.

  • Sweep_The_Leg
    Sweep_The_Leg 🟡

    @jaeW_at_Onyx I never converted it to magic 2.0, and even then, no one ended up using it anyway. kind of disappointing, but oh well :)

Categories