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
**Make sure toany 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
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 466 Domo Developer
- 50 Domo Everywhere
- 106 Apps
- 717 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 27 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 654 ひらめき共有