5 Replies Latest reply: May 15, 2014 10:12 AM by Olly Bond RSS

    Restructuring Data

    kbelli _

      I need to borrow some ingenuity. 

       

      I'm afraid I have to give some backstory here to adequately explain what I'm struggling with. 

       

      Sometimes, I encounter data like this:

       

      Name[/U]  Category[/U]  Value[/U]

      John    A         5

      John    B         6

      Mike    C         7

      /code

       

      [INDENT]But I really want each person to have their own record with the values structured horizontally. 

       

      This is easy enough to do, and it leaves me with data that looks like this:[/INDENT][/INDENT]

      Name[/U]  A[/U]  B[/U]  C[/U]

      John  5  6  -

      Mike  -  -  7

      /code

       

      But today I have the opposite issue.  My data has been given to me in a horizontal structure (like the second code example), and I really want to force it to be vertical.  Basically, I received two records, one each for John and Mike.  What I really want is six records, and it would look like this:

       

      Name[/U]  Category[/U]  Value[/U]

      John    A         5

      John    B         6

      John    C         -

      Mike    A         -

      Mike    B         -

      Mike    C         7

      /code

       

      It seems like this would involve creating two new fields.  One field ("Category") would essentially be an array of values {A; B; C} that would force each record to split into three.  The other field ("Value") would just be a calculated field that pointed to the values in the A, B, or C fields depending on what was contained in the Category field.

       

      I could do this in Excel rather simply, but I would love it if I didn't have to.  Any ideas?

        • Restructuring Data
          Olly Bond

          Hello Kbelli,

           

          Monarch lets you do this - from about v6 and above - using the MultiColumnRegion.

           

          In the Report window, select the first line of detail (John). Don 't create a template yet, but in the Templates menu, set the MCR to be active, with 3 columns, starting in position 6 with a width of 3. Back in Template > New Template, define your detail template and make sure the tickbox for MCR is active. You only need to define two fields - "John" and "5". Now define an Append template with MCR active so that A B and C are picked up.

           

          You should have the data you want in the Table window. MCR is amazingly powerful and let you handle all sorts of tricky problems. Give me a shout if you'd like any help with it.

           

          Best wishes,

           

          Olly

            • Restructuring Data
              kbelli _

              Olly, this is intriguing.  Thank you for your help and for pointing MCRs out.  I had never really used them before, but I suspect I will definitely be playing around with them in the future.

               

              I may have oversimplified the data that I'm dealing with though.  Here is a censored version below.  Is it possible to have multiple columns of varying widths?  Or irregularly spaced?  The data that I'm working with doesn't have any headers, but I'm going to add them in just to point out what I'd like to trap.  And if it simplifies the process, I think I can edit the original data source to provide headers for use in an append template (if necessary).

               

              Policy[/U]                                             Od1[/U]   Nw1[/U]     Od2[/U]   Nw2[/U]     Od3[/U]   Nw3[/U]     Od4[/U]   Nw4[/U]     Od5[/U]   Nw5[/U]     Od6[/U] Nw6[/U]   Od7[/U] Nw7[/U]   Od8[/U] Nw9[/U]   OdW[/U] NwW[/U]       OdX[/U] NwX[/U]   OdY[/U] NwY[/U]

              AA000001 006 Y 1990 001 1A    0 06 00000 Y N N   5 00078 00066 5 00044 00040 4 00012 00012 0 00000 00000 0 00000 00000 2 006 008 2 010 012 0 000 000 0 000 000 00000 000 000 0 000 000

              9 071692   000.00   000000   01 100947 Y 531 0 000   0 0 0 00   0 0 0 F150   F 001 M M 061                                                                               

              AA000002 008 Y 2000 001       0 05 00000 N N     0 00000 00000 0 00000 00000 0 00000 00000 4 00030 00030 4 00034 00034 0 000 000 0 000 000 0 000 000 0 000 000 00000 000 000 0 000 000

              9 071692   000.00   000000   00 000000 N 531 0 000   0 0 0 00   0 0 0 TRAILR T 800     209                                                                                /code

               

              I only want information from records that start with AA00000X, so the 9- records are irrelevant.  This data represents the effect of a proposed rate change on a list of policies.  The Od# series is the old premium, and the Nw# series is the new premium.  Unfortunately there is extraneous information in between these series, and the field widths are not uniform. 

               

              What I would like to eventually turn this into is data that looks like this:

               

              Policy[/U]  Grp[/U]    Old[/U]   New[/U]

              AA000001 1   00078 00066

              AA000001 2   00044 00040

              AA000001 3   00012 00012

              AA000001 4   00000 00000

              AA000001 5   00000 00000 

              AA000001 6     006   008

              AA000001 7     010   012

              AA000001 8     000   000

              AA000001 9     000   000

              AA000001 W     000   000

              AA000001 X     000   000

              AA000001 Y     000   000

              AA000002 1   00000 00000

              ...

              /code

               

              By using a MCR like you suggested (with a column width of 14), I can grab the first five columns of the Od# series.  But beginning at Od6 I start to run into problems.  The field width for the Od# series prior to Od6 is five, but it needs to switch to three thereafter.  Moreover, the column width needs to switch from 14 to 10.  This would grab the next four of the Od# series, and then I would need to reposition again for the final two.  (I am also interested in the Nw# series.  But I believe/hope that, once the Od# series is successfully catpured, the Nw# series will fall into line without much trouble.)

               

              Can this be done?

               

              Thanks again for your help.

                • Restructuring Data
                  Olly Bond

                  Hello kbelli,

                   

                  Yes, you can define columns that can cope with variable widths, and it helps that in your case it looks like all the columns in each row are populated.

                   

                  The trick is to define the MCR with the right number of columns (24? in your case, bearing in mind Monarch's limit of 40), each with a column width of 1, and with the left hand position of the first column way over in the right hand margin of your report (e.g. position 300 or somesuch). Then define your data as one big field, which I usually call , and define a calculated field :

                   

                  lsplit(intrim();24;" ";Column())

                   

                  to return the data you want from each record.

                   

                   

                   

                  Good luck!

                   

                  Olly

                    • Restructuring Data
                      kbelli _

                      This is really slick.

                       

                      So am I understanding this correctly? 

                       

                      A Multicolumn region doesn't actually need to include anything within it from the report.  If I defined a MCR with 5 columns at the right margin of my report, I would effectively be multiplying each record by five?  Each group of five records would be indexed by a column number from the MCR, and I could do various operations on whatever detail information I do pull in based on which column() value the record has?

                        • Restructuring Data
                          Olly Bond

                          Hello kbelli,

                           

                          Thanks And yes, you're understanding it right. I gave an outline of it (for fixing variable width columns in PDF reports) in a webinar for Datawatch a year or so ago:

                           

                          Subject: Monarch Tips & Techniques #5 : Invisible Data

                          Recording URL: https://www117.livemeeting.com/cc/datawatch/view[/url]

                          Recording ID: QPC3BW-1

                           

                          There's a lot more to it than just odd-width columns - for example, you might want to connect data with date ranges to data with specific dates. So table A has got entries that indicate a condition applied on a range of dates like:

                           

                          01.01.2012 - 29.02.2012

                           

                          and table B has got that something that says that an event happened on a particular date:

                           

                          15.02.2012.

                           

                          There's no way a conventional external lookup from B to A will get you a join in this sort of scenario - you need to blow up table A so that you have one record for every date in the range, and this technique, iterated twice, lets you do that.

                           

                          Enjoy the holidays,

                           

                          Olly