DW_ID not updated in unique key field in hgistoric table

Question

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.

icon

Best answer by Christian Hauggaard 11 July 2023, 14:05

View original

Christian Hauggaard · Accepted Answer

Hi @brian.wijningsI 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 executeI 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 viewIf 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 tableIf my data looks like this initiallyAnd 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.

Greg Lennox · Answer

Please clarify the purpose of theeenheidssleutel 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 theeenheidssleutel field would set the value of that field to the DW_Id field value in the event that theeenheidssleutel field is null. However, since it does have the 8089 value already, then it is not null and its value will not be transformed.

DW_ID not updated in unique key field in hgistoric table

3 replies

Reply

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded