Skip to main content
To homepage To homepage
Solved

DW_ID not updated in unique key field in hgistoric table

brian.wijnings
Explorer
Forum|alt.badge.img

We have a historic table and we use Dw_id as an unique indentifier. 

But when we update the record the DW_Id will stay the same, so we have duplicate values in the table on that field then. the original DW_id field is changing but the transformantion field isn’t. 

 

Because we work with custom data aswell that we want to have there own value we work with the custom transformation. I will  provide some screens from the issue: 

 

 

Can you provide me a way so the field eenheidssleutel will be unique over time and is equal to the DW_ID now and in the past of the historic table. 

Best answer by Christian Hauggaard

Hi @brian.wijnings 

I believe I have reproduced the issue you are facing. 

If I have a add a field in my history table and add a custom transformation referecing the DW_Id and then I execute

I then change the name (type 2 field) for record 2 in my source and execute the history table which generates a new record in my history table.

This results in a mismatch between DW_Id and NewID. This is because the value for the custom transformation is from the Raw table, not the valid table. This can be seen under advanced → custom code → transformation view

If you preview the raw table, this explains why the NewID field for the new record gets a DW_Id of 2 rather than 4.

As a workaround, please try using a custom step. 

Then set this custom step to run after execution of the history table

 

If my data looks like this initially

And then I change record 2 in my source again and execute my history table, the NewID field takes on the same value of DW_Id in the valid table using this workaround.

Alternatively you might be able to use a view to achieve the same outcome.

View original
Did this topic help you find an answer to your question?

3 replies

G
Community Manager
Forum|alt.badge.img
  • Greg Lennox
  • Community Manager
  • 27 replies
  • July 10, 2023

Please clarify the purpose of the eenheidssleutel field. In order to amend the transformation, more information on how the field is currently populated would be helpful. In the screenshot above, where is the 8089 value coming from and what does it represent in the table.

From the screenshot above, there are two other fields that appear to be your ID fields. The EenheidID field appears to be similar to an account ID field and the DW_Id field works like a surrogate key, so normally these two are what you need in your history table with regards to the keys.

The transformation for the eenheidssleutel field would set the value of that field to the DW_Id field value in the event that the eenheidssleutel field is null. However, since it does have the 8089 value already, then it is not null and its value will not be transformed.

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi @brian.wijnings 

I believe I have reproduced the issue you are facing. 

If I have a add a field in my history table and add a custom transformation referecing the DW_Id and then I execute

I then change the name (type 2 field) for record 2 in my source and execute the history table which generates a new record in my history table.

This results in a mismatch between DW_Id and NewID. This is because the value for the custom transformation is from the Raw table, not the valid table. This can be seen under advanced → custom code → transformation view

If you preview the raw table, this explains why the NewID field for the new record gets a DW_Id of 2 rather than 4.

As a workaround, please try using a custom step. 

Then set this custom step to run after execution of the history table

 

If my data looks like this initially

And then I change record 2 in my source again and execute my history table, the NewID field takes on the same value of DW_Id in the valid table using this workaround.

Alternatively you might be able to use a view to achieve the same outcome.

brian.wijnings
Explorer
Forum|alt.badge.img

Ok thanks for your reply and extended explenation. 

This is fixing our issue for now, thanks for pointing out the raw table!

Regards, 

Brian

Reply


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings