Skip to main content
To homepage To homepage

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

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:



UPDATE sh1


SET sh1.[Row]=sh2.[Row] --Update rownumber in valid-table


FROM [etl].[Sheet1] sh1


INNER JOIN ( --join row-number

 

SELECT


             a.


            ,ROW_NUMBER() OVER (PARTITION BY a.pOrdeId] ORDER BY a.pSCD From DateTime] ASC) AS >Row] --fetch RowNo



  FROM etl].sSheet1] a


  WHERE EXISTS ( --limit id's from valid table, to whatever found in raw-table



              SELECT b.aId]


              FROM etl].FSheet1_R] b


              WHERE a.Id]=b.aId]


  )

 


) 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