DW_ID not updated in unique key field in hgistoric table
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.
Page 1 / 1
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.
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.
Ok thanks for your reply and extended explenation.
This is fixing our issue for now, thanks for pointing out the raw table!
