"Append to DataSet" method is creating duplicates
In a nutshell:
1) create & build the initial dataset in workbench
SELECT * FROM myTable
-- WHERE StartDateTime > '!{lastvalue:StartDateTime}!'
ORDER BY StartDateTime
2) update job method from Replace to Append:
a) uncomment my WHERE clause in my query
b) update/add "replacement variable" subtab
Column = StartDateTime
Value = !{lastvalue:StartDateTime}!
c) switch dataset to append
d) save & run
Results: the dataset job appends new records as expected, the only issue is the occasional duplicate record (and sometimes triplicate records). I know I can remove the duplicates with a dataflow, but shouldn't have to...
Any ideas why the duplicates are happening?
We also have a couple other append dataset jobs, they've all running fine for months (except for random duplicate/triplicates). I have experimented with other queries, using primary keys instead of DateTime, same issue - happening with all append dataset jobs, not just this specific one.
Thanks
edit: added code tags in attempt to remove emoticon/smiley... : S
Comments
-
I can't tell you why, but I can tell you that we experienced some of the same issues with replacement variables.
In some instances we decided to take a different approach by creating multiple replacement datasets of different timeframes and stacking them into one.
In other cases we've kept the append, removed the replacement variable, and used a relative date, like trx_date=sysdate-1, or something similar. We haven't seen duplicates with this method.
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"1 -
Hi,
So the Problem which your curently facing is because timestamp value which your using for incremental records. Even i have faced the same thing and have dropped a mail to DOMO support but as of now you cant control the syntax of the replcament variable , so you have to use the data flow for creating this.
If our database has date insted of timestamp use that am sure you wont se any duplicates , also make sure to cast it in the right way.
0
Categories
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 524 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 510 Automate
- 114 Apps
- 388 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 67 Community Announcements
- 4.8K Archive