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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 6 Cloud Amplifier
- 10 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 780 Automate
- 191 Apps
- 482 APIs & Domo Developer
- 84 Workflows
- 23 Code Engine
- 41 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 2 AI Projects and Models
- 18 Jupyter Workspaces
- 414 Distribute
- 122 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 146 Manage
- 142 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive