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

    Combining 2 different .txt reports with 1 common field

    Lianne _



      I'm hoping someone is able to help me? I have 2 different reports containing invoice information. Both contain the invoice number as a common field but all other data (and format) differs.


      I need to obtain all fields from one report and then I also need one field from the 2nd report.


      Normally addidional data I add in would be in excel format so I would create a Lookup but both of these are text files so I'm unsure of the simplest way I can do this...


      Is there any way I can load both reports at once to pick up the required fields and for monarch to show these in one combined table as if derived from one report?

        • Combining 2 different .txt reports with 1 common field
          Olly Bond

          Hello Lianne,


          In exceptional circumstances with very well behaved data you might get away with this in one model using the floating trap, some calculated fields and a summary - if you can post samples from the two reports here between and tags I could let you know if there's a chance of that approach working.


          But if you have Monarch Pro, then you can do this easily with a two-stage approach. Open Report A, and trap your data, and export the table to an Access file (.mdb extension). Then close Monarch. In another Monarch session, open Report B, trap the data from this, go to the table window, and select Data > External Lookups, and define a new external lookup to the Access file created in the first stage.


          Join the datasets on the invoice number and select the required fields.





            • Combining 2 different .txt reports with 1 common field
              Lianne _

              Hi Olly,


              I have Monarch pro so I will start working on your second suggestion. Thanks for your help!



                • Combining 2 different .txt reports with 1 common field
                  Grant Perkins

                  Hi Lianne,


                  A quick question.


                  Are you able to treat one of the reports as the 'Master' report and the other as the one you want drag the extra field from?  If so the extract from the non-master report and using that as a look up is the way to go, as Olly said.


                  If, however, you need to include invoices (by number) that don't exist on both reports but still need to be listed, then a slightly different approach would be required. Same principle of a multi-step process though. Probably ...


                  I'll assume you have scenario 1 but if it is more than that let us know and we can come up with some further ideas.