6 Replies Latest reply: May 15, 2014 10:16 AM by Olly Bond RSS

    One to many help needed.

    lcwalker _

      Good afternoon, any help would be appreciated.  I am performing an ETL process from a legacy system to a new enterprise software.  The legacy system stores product information like so:

       

      ITEM   | ISSUE_UOM | PURCHASE_UOM | FACTOR

      ABC123 | EACH      | CASE         | 20

      XYZ456 | BAG       | BOX          | 12[/CODE]

       

      With item ABC123 being issued by the each (1 each per each), but purchased by the case of 20 eaches, item XYZ456 is issued by the bag (1 bag per bag), purchased by the box of 12 bags.

       

      The new system records the same information like this:

       

      ITEM   | UOM  | FACTOR | ISSUE_UOM | PURCHASE_UOM

      ABC123 | EACH | 1      | YES       | NO

      ABC123 | CASE | 20     | NO        | YES

      XYZ456 | BAG  | 1      | YES       | NO

      XYZ456 | BOX  | 12     | NO        | YES

      /CODE

       

      Does anyone have any idea how I can accomplish this with Monarch (10.5), or a combination of Monarch and Excel?  The extract file contains about 25k rows, which will transform to between 50k to 75k rows in the new system.

        • One to many help needed.
          Olly Bond

          Hello

           

          Yes, this is easy in Monarch. V11 would help but it should be possible in 10.5.

           

          If your input file is s text report or CSV, then it's a one model fix.

           

          If your input file is Excel, then it's a two stage process.

           

          I'm on vacation for a few days and away from Monarch - can this wait until next week?

           

          Best wishes,

           

          Olly

            • One to many help needed.
              lcwalker _

              Thank you Olly.  Yes, I can wait for a solution.  I've got other data elements I can work on in the mean time.  Also, the data is currently in Excel.

                • One to many help needed.
                  Olly Bond

                  Hello LC

                   

                  If you're starting from Excel then it's a definitely a two model job. The first model is to take the Excel and export the table as a fixed width text report. You can filter the data at this stage and select only the fields you need also.

                   

                  The second model is to open this fixed width text report and apply a multi-column region to it, with two columns, each 1 character wide, starting in column 200 (or wherever is safely to the right of the data. Then we can start to get the data into shape.

                   

                  If you can email me a sample I should be able to have a bash at the data over the weekend.

                   

                  Best wishes,

                   

                  Olly