How to continue unique IDs for rows?

[RHehlmann]

Hello,

I have a data set consisting of quarterly data. Within each quarter, the ordering of the rows is important. Hence, I used a tile tool to determine the ordering of the rows for each quarter. Now I have to add data for various quarters and the new data rows have to be below the existing data rows in each quarter.

I'm currently thinking about the following solution:

1. Filter out new data rows
2. Use the tile tool on the new data
3. Add a sufficiently large number (e.g. 1,000) to Tile_SequenceNum
4. Union my data streams back together
5. Sort by 1. Quarter 2. Tile_SequenceNum -> this should give me the data rows ordered for each quarter with the new data rows on the bottom and the ordering within my old and new data rows remaining the same as in my input data
6. Use the tile tool again on quarter.

My question is: Is there a simpler approach to basically just start "continue Record ID based on latest value for each tile"?

Thanks and best regards

[Syarifhidayat]

@RHehlmann,

For this problem, you can connect Formula tool  with Tile tool. Then, create a formula by update your ID column using this formula

[field1]+2

change value of 2 to the latest ID value, so your ID will be updated based on the latest value for each tile.

Hope this helps!

[danrh · Accepted solution]

You should be able to accomplish this with the Multi-Row Formula tool:

I'm assuming from your post that you already have an ID field that is incrementing within each quarter - if not, you could get fancy using a Summarize tool to find the highest ID for a given quarter. The key thing here is that in the Union tool make sure to "Set a Specific Output Order" and put the new data below the old data. Then in the Multi-Row Formula tool, select to group by your Quarter field and add an expression that is adding 1 to the previous row's ID field.  As long as your order is correct before starting (which your union tool takes care of), I think this should produce the results you need.

[RHehlmann]

Multi-Row Formula works, thanks a lot.