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

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