Tried ETL Rank & Window and MySQL, but just can't crack this. If my field is null, then give me the previous value.
Probably going to have to dive into a MySQL dataflow.
Here are some potential resources:
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
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?