6 Replies Latest reply: May 15, 2014 10:05 AM by baghu _ RSS

    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

        • Position of codes using database import
          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[/code]

           

          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[/code]

           

          /quote

           

          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.

            • Position of codes using database import
              baghu _

              Grant,

               

              Thank you very much for the quick response.

               

              The original source is csv file.

               

              Regards

              Baghavathy

                • Position of codes using database import
                  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

                    • Position of codes using database import
                      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

                        • Position of codes using database import
                          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:

                          [SIZE=2]

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

                          /SIZE[/CODE]

                           

                          I then added a Numeric calculated field named KeyRecord:

                          [SIZE=2]

                          Recno()

                          /SIZE[/CODE]

                           

                          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:

                          [SIZE=2]

                          RecNumber-KeyRecord+1

                          /SIZE[/CODE]

                           

                          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.