4 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Extract data from Multiple Column

      I have a report which has various columns, i would like to extract data from the report with reference to the value in the column heading..sample report as below...

       

      Acct_Type     RPT_001   RPT_002

      act_1               25000         2500

      act_2                   125          326

       

      Acct_Type     RPT_003   RPT_004

      act_1                 2542         2342

      act_2                   123          323

       

      the number of columns (RPT_001, 002) may vary upto 6 columns and the number of section may also vary

       

      i need monarch to extract me a report in the following format..

       

      Acct_Type  RPT_Source  Value

      act_1         RPT_001       25000

      act_2         RPT_001          125

      act_1         RPT_002        2500

      act_2         RPT_002          326

      act_1         RPT_003        2542

      act_2         RPT_003          123

      act_1         RPT_003        2342

      act_2         RPT_003          323

       

       

      can you please suggest me as to how i go about laying traps !!

        • Extract data from Multiple Column
          Grant Perkins

          Hello dvivek and welcome to the forum.

           

          In theory you should be able to use the Multi Column Region capability to extract your columns as DETAIL templates complete with the column headings extracted as APPEND templates. However the big question is whether your import report is consistent enough in its horizontal format  to allow you to do this easily. In some situations the positioning of the column header relative to the data can be problematic  - for instance if ther is a long description line that may insert unwanted characters into a between the header and the detail in an MCR area. It is difficult to make sense of the problem by description until you have seen it. It is a rare problem but can be confusing if it appears.

           

          So the important message here is that the appraoch and the results are very much dependent on the actual layout of the real report. The conceptual layouot as you have posted is fine for outline understanding of the problem but may not help to specify a suitable approach.

           

          So far it sounds like you will need some interesting advanced techniques and that the trapping part of the templates in the model will only be a part of your complete model solution.

           

          So, some questions:

           

          Do all of the different formats appear in a single report or are you talking about several reports which are similar but different?

           

          Have you used Multi Column Regions before?

           

          Are all of the columns in the report(s) the same width? (Or can they be condiered the same width for processing purposes even if the actual data widths are different?)

           

          Let us start here and see how the approach develops.

           

           

          Grant

            • Extract data from Multiple Column

              Hi Grant,

               

              Thanks for your reply..

               

              to answer your questions...

               

              1. All of the formats are in the same report.

              2. I havenot used MCR before - am trying to figure this out through the user guide

              3. Yes the column width can be considered as same. The only catch is we wont know how many columns would be there in a report.

                • Extract data from Multiple Column
                  Grant Perkins

                  OK lets see what we can do.

                   

                  Assuming that the MCR stuff is suitable (it should be) there is a standard approach that seems applicable here and there is a more complex approach we can consider if you run into trouble with the standard approach.

                   

                  You need to have enoough columns for the max numbero f columns in any part fo the report. Empty columns (in temrms of the template definition) are normal and OK.

                   

                  I remember one report some years ago that was also several sections with apparently different formats but it turned out that they were nearly all the same, The look of each section was different only because they were centred on the page (with two small exceptions that we could work around).

                   

                  If your report is similar one trick might be to see how the report looks if all lines are left justified (I assume left would be correct but I guess right justified is also a possibility.) To do that set a simple trap with no trapo characters and select the entire line as a field. IN the table use a calcualted field to LTRIM any space at the start of each  line ans then see if the columns match.

                   

                  If they do then you can export the trimmed report and model more easily against the new version. However I think with the other example we may simply have skipped the MCR aspect and just used a "slice and dice" technique for the data extraction for the one long field. It really depends on the data and format. If you have variable column headings that sit in their own line and need to be part of the extracted table because the values cannot be derived any other way some of these concepts may not be very useful for you. But MCR should work well if the formats can be made 'common' to all sections of the report.

                   

                  MCR is basically quite easy but can look more complicated than it is. Difficult needs can indeed become more complicated. The learning guide is a good place to start so when you have learned what you can from that you will be well placed to understand what questions you still need to ask and, hopefully, the answers when they come back!

                   

                  HTH.

                   

                   

                  Grant

                    • Extract data from Multiple Column
                      Olly Bond

                      Hello Dvivek,

                       

                      From what you've described, I would have thought that the variable-width MCR trick might be helpful.

                       

                      If you know the maximum number of columns (n) you're going to encounter, then create an MCR region with n columns, each of width 1, starting position a long way to the right of the data (say, column 100).

                       

                      In Monarch v10, there's a function called Column() which can help, in v9 it's slightly trickier but you can do it using Mod(), Rowno() and a Sort on Line() and Page(). That method is written up in a newsletter from last July, I think.

                       

                      In your template, select the whole line as one big field, called, say, "blob". Then use:

                       

                      Val(Lsplit(Intrim(Blob);n;" ";Column()))

                       

                      These does require your data to be relatively well behaved (consistently present, without spaces in the data) but we can work around that if you need. You will have to apply the same logic to the Append level as well.

                       

                      HTH - feel free to drop me an email with sample data if you want me to bash out a quick model for you.

                       

                      Best wishes,

                       

                      Olly