My input database is like the below table.
scan # Code
My output is required with a addition of a new column as follow.
scan # code code position
1 12345 1
1 45678 1
12 89673 1
15 12345 1
34 12365 1
That is a somewhat unusual looking database input. What is the source? Is it a direct link to a database or a csv file or Excel or something like that?
PS. attempted auto ormatting did not work as hoped in both cases.
OK, so it looks something like this to explaion the missing scan numbers?
(or the equivalent for the scan# if it is not the first field in the file ...)
I am considering whether in this case reading the csv file as a report (or converting it to a report style fixed width output using Monarch Utility) might offer a better way forward.
It takes a couple of passes, but I have a potential solution.
In the first model, I opened the csv file as a database This gave me the fields named F1 and F2. I added a Character calculated field named Key with this formula:
I then added a Numeric calculated field named KeyRecord:
As a project export, export this as a fixed-length text file. Save the model and the project for later reuse.
Start a new model, opening the new text file as the data source. Build a new template to pickup all the rows with a value in F2. This gave me F1, F2, Key, and RecNumber.
Set the Key field to Copy value from previous record.
In the Table window, create (and run) a new project export to Lookup.xls.
Create a new external lookup, connecting to Lookup.xls, based on Key number.
Bring in KeyRecord, and build a new Numeric calculated field named Sequence:
Hide the now superfluous fields Key, RecNumber and KeyRecord.
Save the model and the project for reuse.
In regular use, if you'll go through this process frequently, you'll want to create a small batch file which opens the the first project and creates an export, then opens the second project and creates an export, then re-opens the second project which then refreshes itself with the newly created export.