lastvalue not updating when setting up UPSERT
I'm using Workbench 5 to set up some new datasets, on transactional tables which are updated often.
I'm testing this on a simple example, and have followed the guides https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Workbench_5/Using_Upsert_in_Workbench_5 , Section How Do I Change Data Capture in https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Workbench_5/13Workbench_5_FAQs and also from this post https://dojo.domo.com/t5/Workbench/How-do-I-use-lastvalue-variable-in-workbench-4/m-p/9244#M188
My query is set as this:
select id, last_updated, skills
from domo_test
where last_updated > !{lastvalue:last_updated}!
order by last_updated
I created the dataset using an initial value last_updated as to_Date('01/01/2015','dd/mm/yyyy') as the per the documentation. Once loaded, I changed the Update Method to Append, and selected the ID as my Upsert key on the schema tab.
When I run the job, there is no change in the query - I cannot see any change to the variable , it remains with the intial date set, and continues to load the same query with the intial date.
Using the solution in https://dojo.domo.com/t5/Workbench/How-do-I-use-lastvalue-variable-in-workbench-4/m-p/9244#M188 also does not work - if I set the value of the variable to !{lastvalue:last_updated}! and run the job it ends up in an infinite loop.
How is the lastvalue variable set, and can this be seen anywhere in Workbench?
Best Answer
-
Found the solution.
Even though the replacement variables work to replace the initial value irrespective of case, the lastvalue does not update unless the case of the column name exactly matches the name on the schema tab.
For my example, whilst last_updated ran fine initially, the lastvalue would only be updated if I named the column LAST_VALUE in both the query and the replacement variables.
0
Answers
-
Found the solution.
Even though the replacement variables work to replace the initial value irrespective of case, the lastvalue does not update unless the case of the column name exactly matches the name on the schema tab.
For my example, whilst last_updated ran fine initially, the lastvalue would only be updated if I named the column LAST_VALUE in both the query and the replacement variables.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 743 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive