2 Replies Latest reply: May 15, 2014 10:06 AM by Grant Perkins RSS

    Exporting to same table 2 different sources

    rob.lyles _

      Hi,

      I was wondering if someone could help me with the following please. I have a table in my db for line item details in an order. The data in this table must come from 2 different reports because the way my company has designed the reporting.  In other words, each report contains fields that the other report may not concerning the same line item for the same order. Can Monarch export the fields I need from the 2nd report to the same table from the 1st report? Or do I have to create 2 seperate tables and rebuild what I want to see in a query? My primary keys exist in both reports. Please see below.

       

      (This example is what I am hoping to achieve)

      Line Item Detail Table:

      Field:         Source:

      Order #      Report 1 (Key)

      Line #        Report 1 (Key)

      Part #        Report 1 (key)

      Description  Report 1

      Cost          Report 2

      Price          Report 2

      How Price   Report 2

       

       

      Thanks,

      Rob

        • Exporting to same table 2 different sources
          Data Kruncher

          Hi Rob,

           

          It sounds like you've got a good handle on this one. This will be easy if you have Monarch Pro, but I can't tell from your profile.

           

          With model #1 you want to export data from Report1 to TempTable1.

           

          With model #2  you want to export data from Report2 to TempTable2.

           

          Then model #3 opens TempTable1 as a database source. Build an external lookup to TempTable2 using all three of the Order #, Line # and Part # fields as the key.

           

          You can then export from here to your production database.

           

          If this will be a regular process you can automate the three steps pretty easily with a small batch file to make the work easier.

           

          HTH,

          Kruncher

            • Exporting to same table 2 different sources
              Grant Perkins

              Rob,

               

              Do all the order lines you are interested in always exist on both reports?

               

              If so you could follow Kruncher's plan successfully but I wonder if you also have the option (using Monarch PRO) to create an extraction from one of the reports based on the key fields you have identified and export that as suggested and then us it as an External lookup directly in the second report/model combination. That would save one model and process step.

               

              If you don't have Monarch PRO it MIGHT be possible, running interactively, to do something similar. Extract the first lookup data and then cut and paste it into an INTERNAL lookup in the model. This would probably work best with a single field to look up but I could see possible ways to be flexiboe with what is in the single field and what to do with it after it has been added to the second process. If you have a lot of records in the result this may not be such great idea. One can't be sure without testing it.

               

              If, on the other hand, the 2 reports can have key lines that are NOT COMMON, then you would probably do best to create a master file list of all possible lines in both reports (eliminate duplicates) and then link both sets of lookups created, as Kruncher described, one per report. From what you wrote I dont think you need to do that but if you do I can provide a more detailed description of the concept.

               

              HTH.

               

               

              Grant