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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 680 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive