CASE WHEN X IS NULL THEN Previous Value

Sweep_The_Leg

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

Account	Date	Cost
1205948	15-Aug-2019	$  42,246.48
1205948	16-Aug-2019
1205948	17-Aug-2019
1205948	18-Aug-2019
1205948	19-Aug-2019	$  42,246.48
1205948	20-Aug-2019	$  20,022.44
1205948	21-Aug-2019
1205948	22-Aug-2019
1205948	23-Aug-2019	$  46,556.96
Original
	Desired
Account	Date	Cost
1205948	15-Aug-2019	$  42,246.48
1205948	16-Aug-2019	$  42,246.48
1205948	17-Aug-2019	$  42,246.48
1205948	18-Aug-2019	$  42,246.48
1205948	19-Aug-2019	$  42,246.48
1205948	20-Aug-2019	$  20,022.44
1205948	21-Aug-2019	$  20,022.44
1205948	22-Aug-2019	$  20,022.44
1205948	23-Aug-2019	$  46,556.96

guitarhero23

Probably going to have to dive into a MySQL dataflow.

 

Here are some potential resources:

• https://stackoverflow.com/questions/26725119/fill-empty-row-with-value-of-previous-row-mysql
• https://stackoverflow.com/questions/31276169/how-can-i-fill-empty-cells-with-values-from-previous-rows

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

 

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

nicangelica

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

jaeW_at_Onyx

@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

@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

@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

@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

@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 :)