To homepage To homepage
Solved

Seeking Best Approach to Add a Counter to a Table That Resets Based on Another Field

  • 21 February 2020
  • 2 replies
  • 39 views
G
Rank

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

icon

Best answer by martin.dideriksen 24 February 2020, 08:08

View original

2 replies

S
Rank
Userlevel 3
Badge +3

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

M
Rank
Badge

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:

UPDATE sh1
SET sh1.[Row]=sh2.[Row] --Update rownumber in valid-table
FROM [etl].[Sheet1] sh1
INNER JOIN ( --join row-number
 
SELECT
             a.[DW_Id]
            ,ROW_NUMBER() OVER (PARTITION BY a.[OrdeId] ORDER BY a.[SCD From DateTime] ASC) AS [Row] --fetch RowNo
  FROM [etl].[Sheet1] a
  WHERE EXISTS ( --limit id's from valid table, to whatever found in raw-table
              SELECT b.[Id]
              FROM [etl].[Sheet1_R] b
              WHERE a.[Id]=b.[Id]
  )
 
) sh2 ON sh1.[DW_Id]=sh2.[DW_Id]

 

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

 

 

Reply


Terms and ConditionsCookie settings