2 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    How to extract multiple lines and types of data??

    wancherng _

      Hai all, i'm new to Monarch and jz using Monarch Pro8. i have a report like below and found some issues to extract the data that i need.

       

      i jz need the line of "Currency", and map to the line of "Total Equity" and "Net Equity".

       

      But the problem are the currency entris are not consistent, that means sometimes there are few available currencies or sometimes there could be more. This give me a problem to map the value of the "Total Equity" accordingly. And also I don't want the column "Base USD".

       

       

      [font="courier"]      Account Number: 123ABC         Client Account Number: 123ABC        Account: Sample Company                                                                               

      FX Rate                                 0.1212000     0.9876543         0.3434500            1.5555000           0.2323000               0.0012345         1.0000000            1.0000000                                                                    

            Currency                                      AUD           CAD               EUR                  GBP                 HKD                     JPY               USD             Base USD                                                                    

            RegCode                                                                                TOTAL                                                                               

      Blah Blah1                              12,345.67        123.45         12,345.67               876.54       56,789,000.00          123,456,789.00         12,234.56        3,456,789.712                                                                    

            blah blah2                                   0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            blah balh3                                   0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            blah blah4                                   0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            blah blah5                                   0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            blah blah6                                   0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            blah blah7                              12,345.67        876.54         12,345.67               876.54       12,121,212.12          343,434,343.34         23,232.32         1,111,111.11                                                                               

      ABC CBA ABC                                  0.00          0.00              0.00                 0.00       -2,940,000.00            7,150,000.00              0.00          -316,716.69                                                                    

            ABC Function SET                             0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            Total Equity                            88,888.88        777.77         66,666.66               888.77           55,555.55          555,555,444.44         44,444.44         3,333,333.33                                                                    

            Forward                                      0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            Total Equity Inc Fwd Cash               14,988.77        785.38         13,585.60               871.78       13,250,000.00          192,863,754.00         42,228.84         3,430,551.03                                                                               

      Value                                        0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            Option                                       0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            Initial                                      0.00          0.00              0.00                 0.00       -9,202,400.00          -89,375,000.00              0.00        -1,949,453.83                                                                    

            Col/Sec                                      0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                               

      Net Equity                              99,999.77        123.45         99,999.66               123.45        9,099,999.55          123,456,789.00         99,999.44         9,999,999.33                                                                    

            Net Equity Inc Fwd Cash                 12,121.21        121.21         13,585.60               232.32        3,434,343.43          454,545,454.54         22,222.22         1,111,111.11                                                                    

            Blah Blah Value                              0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            Blah blah blah                               0.00          0.00              0.00                 0.00                0.00                    0.00              0.00                 0.00                                                                    

            Liq                                     14,988.77        785.38         13,585.60               871.78       13,250,000.00          192,863,754.00         42,228.84         3,430,551.03                                                                               

      /font[/quote]Then, the output or the final exported table that i want is in this kinda format(in excel):

      [font="courier"] currency | Total Eqty   | Net Eqty

      -


         AUD   |     88,888.88 |     99,999.77

         CAD   |        777.77 |        123.45

         EUR   |     66,666.66 |     99,999.66

         GBP   |        888.77 |        123.45

         HKD   |     55,555.55 |  9,099,999.55

         JPY   |555,555,444.44 |123,456,789.00

         USD   |     44,444.44 |     99,999.33

      /font[/quote]Is there any idea how to do it?

      As the currency code are different data type to the equity value below.

      Do i need to use few models to extract them seperately then append?

      use macro in excel to set the transpose?

      Any u guys got some better ideas?

      Anyway, i hope i'll be relied very soon.

      And all suggestion will be highly appreciated.

       

      Thank u guys in advanced.

        • How to extract multiple lines and types of data??
          Grant Perkins

          Hello and welcome!

           

          I just wanted to check with you that the column widths for the values are as variable as they are in your posted sample. It looks like the width of the column varies according to the currency. Is this the case or is it possible that something happened to the widths during the editing to disguise the data or the posting of the example lines?

           

          Grant

          • How to extract multiple lines and types of data??
            Grant Perkins

            Having refreshed my knowledge on the Multi Column Region functionality ...

             

            If the columns are all the same width (or the report can be manipulated so that they are ...) and the positioning of the data in the columns is consistent, then you can get what you want as far as I can see.

             

            Using a cut and paste copy of your example, but only  the first 2 columns because they ARE consistent, as an example:

             

            Set up the MCR to 2 columns, left position of first column 42, width of column 14. You may need to adjust those values if your original report is slightly different.

             

            Select one of the value lines for the detail template. Make sure you are seeing the screen in MCR design view (One column showing the fields that will be selected.)

             

            Trap on the decimal point. Define the value field and the field outside the columns to the left as the description of the value. At this point we will just capture all lines with values and then filter them in the table to reduce to just the 2 values you want in the summary.

             

            Save the detail template.

             

            Define an Append template for the CURRENCY line, trapping on the 3 char currency codes and using the code positions for the field. Save that.

             

            If you now go to the table you should see currency codes for the first 2 cols we have used, the values and the description of the value line.

             

            Now create a filter that that retains just the Net and Total Equity lines. The easiest way may be to define a filter for description - "Total Equity" and another for "Net Equity" and then use a Compound filter to put both of them together and use the 'either value' option.

             

            Your table will now have just the fields you wanted (but so far ony from the first 2 columns.)

             

            Now create a summary. The fields will be the currency code field, the value DESCRIPTION field and the value field as the MEASURE.

             

            Set the properties (right click on the field in the summary) for DISPLAY of the DESCRIPTION field to ACROSS. Apart from playing with the descriptions if you want to set the columns in a specific order that is the result you want.

             

            Except that it only covers the first 2 columns ...

             

            If the original report does have variable column width then you would need to pre-process the original file for better consistency of column width. You can do that with Monarch as well by selecting every line as a single field (and maybe filtering out lines you don't need?) and then creating calculated fields for each column section of the line. It's easier than it sounds.

             

            Make each new calculated field the same size and make sure that size is at least as wide as the widest currency field you will ever need.

             

            Export the resulting (filtered?) table of calculated fields to a fixed width text file.

             

            You will now have the report in a format with consistent column widths. Create an MCR function based model to use for the new version of the report and the full result for all columns should be available.

             

            One caution though - there are restrictions on the number of columns that can be processed. If you are likely to exceed that because you have too many currency columns to process it may be necesary to look at running the final analysis (or maybe even the file 'conversion') in more than one process with variations on the model to capture different columns.

             

            I hope this helps and provides some ideas.

             

            Good luck, let us know whether you succeed or find some other problems.

             

            Grant