I have a table and I need to have a counter that restarts based on another field on the table.
In my case I have a JournalBatchNumber field. For each new batch, I need to generate a LineNumber restarting with 1 for each record of the same batch.
Is this available in TX natively, or do I need to write a post processing script using rownumber ().
Thanks,
Greg
Best answer by martin.dideriksen
As mentioned by Syed, you can use following code in a custom field transformation.
ROW_NUMBER() OVER (PARTITION BY [OrderId] ORDER BY [DW_TimeStamp] ASC)
But please note, that there is one caveat with this solution. The field transformation is only applied in your transformation-view. This mean that, IF you have a table with an incremental load, you will only have this logic applied to whatever data you have in your "raw-table".
If your Jounalbatch data is inserted from many incremental loads, this logic will not work.
One option, in that case, would be adding a post-script (post data-cleansing) to your table, which updates all the id's in your raw-table. This could look like this:
If you only have a limited no of rows, this solution would be slightly overkill. But if you need something to perform on an incremental load, this would be my best guess.
PS: Remember to add an index on the Id-field (in my case "OrderId")
//Martin
Hi Greg,
The following tutorial shows how to use the SQL Server ROW_NUMBER() function to assign a sequential integer to each row of a result set. Review the example with Partition.
https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-row_number-function/
To integrate this into a Discovery Hub project:
Add a new field to the table, then
Right-click on the field to select “Field Transformations”
From “Field Transformation” pane (on right hand side),
select Operator = Custom value
and click the “Add” button to enter your SQL expression with ROW_NUMBER() function in “Transformation Custom SQL” window.
Thank you!
Syed
As mentioned by Syed, you can use following code in a custom field transformation.
ROW_NUMBER() OVER (PARTITION BY [OrderId] ORDER BY [DW_TimeStamp] ASC)
But please note, that there is one caveat with this solution. The field transformation is only applied in your transformation-view. This mean that, IF you have a table with an incremental load, you will only have this logic applied to whatever data you have in your "raw-table".
If your Jounalbatch data is inserted from many incremental loads, this logic will not work.
One option, in that case, would be adding a post-script (post data-cleansing) to your table, which updates all the id's in your raw-table. This could look like this:
If you only have a limited no of rows, this solution would be slightly overkill. But if you need something to perform on an incremental load, this would be my best guess.
PS: Remember to add an index on the Id-field (in my case "OrderId")
//Martin
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
