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.
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 |
Comments
-
Probably going to have to dive into a MySQL dataflow.
Here are some potential resources:
0 -
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 a3 -
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?
0 -
0
-
@Sweep_The_Leg Hi! I have the same problem with my dataset. May I know how you did it?
0 -
@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.
3 -
@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! 😀
1
Hey, Stranger!
Categories
- 5.5K All Categories
- 10 Domo News
- 8.2K Using Domo
- 1.5K Dataflows
- 2K Card Building
- 1.1K Connectors
- 298 Workbench
- 640 New to Domo
- 78 Dojo
- Domopalooza
- 1K 日本支部