MYSQL Transform: Replacing date on historical input based on update of second input
I'm looking for the best way to do this. I have a SQL Transform with a recursive input and a second input which I'd like to have replace the input of the recursive data based on the date in one of the columns of the second input. The pictures below should help because I'm just as confused explaining this as you probably are reading it.
This image shows the historical data. Every day, the current date is appended to this dataset. Note the Date column (not batch date).
This one shows the monthly update. Note the Date and Batch Date columns. Since this file gets finalized after the end of the month, we want this to replace what's in the historical dataset using the Date column.
Comments
-
Can you just join the new with the old on the GL account code and in the output use the new date in place of the old date?
Instead of
SELECT
old.`Date`, old.`GL Account`, old.`Gl Name`, old.`Balance`, old.`Batch Last Run`
FROM
historical as old
Use something like:
SELECT
new.`Date`, old.`GL Account`, old.`Gl Name`, old.`Balance`, old.`Batch Last Run`
FROM
historical as old left join monthly as new on old.`Date` = new.`Date`
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Would that replace the data in the `Balance` field for that date only? I still want to keep the rest of the data on the dataset.
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
- 692 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