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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 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
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive