Assembler Upsert Date Column
Hi. I need to take a snapshot of today's data in order to compare it with tomorrow's data once it will be available. The problem is that the SQL dataflow and Magic ETL only can use Replace method. I need to find a way how to append the data each day to the already existing historical table.
I created Assembler and I chose Assembler Type as Upsert. I got an error message. Please see below the setup and error message.
Comments
-
Just in case you aren't already aware, source datasets can be set to replace or append. They don't all have to be replace.
Additionally, you can use recursive dataflows to create an appended dataset. See these two KB articles:
https://domohelp.domo.com/hc/en-us/articles/360043428133-Creating-a-Recursive-Snapshot-SQL-DataFlow
**Check out my Domo Tips & Tricks Videos
**Make sure to
any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Hi @user095063
This is because you already have a dataset with the date listed multiple times. There are a few options to do an upsert using Magic ETL 1, Magic ETL 2.0 and MySQL. Note that as your dataset grows these will take longer to run.
Magic ETL 1.0: Creating a Recursive/Snapshot Magic ETL DataFlow
Magic ETL 2.0: Creating a Recursive/Snapshot DataFlow in Magic ETL v2 (Beta)
MySQL: Creating a Recursive/Snapshot SQL DataFlow
If you don't care about potential duplicates you could configure the Dataset Copy connector and set it to append and have it run on a daily basis then use a dataset view to append your historical and now live/historical-ish dataset if you can't recreate your historical dataset. (You may be able to configure the connector to first pull the historical dataset then change it to pull the daily dataset instead on a daily basis)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
you can hack Domo ETLs and make a dataflow APPEND instead of Replace.
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
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 561 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive