Position of codes using database import

[baghu]

Hi,

I'm using Monarch version 8 and I've the below requirement when I'm importing a data base.

My input database is like the below table.

scan # Code
1 12345
23456
34567

1 45678
34587
12345

12 89673
12345

15 12345

34 12365
23456
12348
45678

My output is required with a addition of a new column as follow.

scan # code code position
1 12345 1
23456 2
34567 3

1 45678 1
34587 2
12345 3

12 89673 1
12345 2

15 12345 1

34 12365 1
23456 2
12348 3
45678 4

I would appreciate any help on this. If you seeing the message not with the correct format, please read the data as follows. Code level is always the position like 1, 2, 3 or 4, code is always a 5 digit and the scan # is the remaining characters. Actually in the output it is three columns.

Thanks
Baghavathy

[Grant Perkins]

My input database is like the below table.


scan # Code
1 12345
23456
34567

1 45678
34587
12345

12 89673
12345

15 12345

34 12365
23456
12348
45678

My output is required with a addition of a new column as follow.


scan # code code position
1 12345 1
23456 2
34567 3

1 45678 1
34587 2
12345 3

12 89673 1
12345 2

15 12345 1

34 12365 1
23456 2
12348 3
45678 4



baghu,

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?


Grant

PS. attempted auto ormatting did not work as hoped in both cases.

[baghu]

Grant,

Thank you very much for the quick response.

The original source is csv file.

Regards
Baghavathy

[Grant Perkins]

baghu,

OK, so it looks something like this to explaion the missing scan numbers?

1,12345
,23456
,34567

1,45678
,34587
,12345

12,9673
,12345

15,12345

34,12365
,23456
,12348
,45678

(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.


Grant




Grant

[baghu]

Grant,

I've got your idea, but still wanted to count the code position of as follows.

SCAN#1, Code1 as 12345, Code2 as 23456 and code 3 as 56734

Please let me know if this is not clear and share with me your email, I will forward the original document.

Regards
Baghavathy

[Data Kruncher]

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:


trim(str(F1))+"-"+trim(str(F2))


I then added a Numeric calculated field named KeyRecord:


Recno()


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:


RecNumber-KeyRecord+1


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.

[baghu]

Hi all,

Thank you very much for the suggestion and it worked well.

Regards
Baghavathy