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:
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**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.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4 -
@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 -
@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.
0 -
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.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
@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 :)
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive