lastvalue not updating when setting up UPSERT

user078707

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?

 

user078707

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.

user078707

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.