Generating and joining incremental numbers based on a boolean input

Question

Currently I have a dataset of unique records to be loaded with a numeric ID starting from 00001 and adding 1 for each record after (so if 16001 records are loaded then the final number assigned is 16001), while some duplicates needed to be pulled out and validated. Once validated, these originally duplicate records are to be brought back into the dataset and reassigned a numeric ID starting from the final number of the original unique dataset mentioned above and adding 1 for every additional record.

The duplicate values being brought back in have a boolean identifier (1 or 0), where records with a 1 will be reintroduced in the original dataset and reassigned a number starting from the last number of the original dataset, and records with a 0 will be excluded.

My current ideas are not working. My best guess was using the Last function of the Summarize tool to pull the final value of the unique dataset, then using a Multi-row tool to generate the additional numbers (Row +1 function) but the rows to be added need to be hardcoded (to my knowledge) so no luck there.  I need help coming up with a catch-all solution that doesn't require hardcoding the final number every run of the workflow.

Thanks in advance.

DawnDuong · Answer

Hi @wronglydone

a general schematic of how this can be done:

1) sort your data by RecordID and a 2nd field (optional, only needed if you have specific rule to select which record if there are multiple instances of the same RecordID)

2) use unique tool. The U output gives you the first instance each unique RecordID whereas the D output contains all the “duplicates”.

3) use a union tool to combine the U output and D ouput (must be in this sequence)

4) add a RecordID tool immediately after the Union tool.

dawn

dougperez · Answer

Can you provide a mock data to me?