8 Replies Latest reply: May 15, 2014 10:04 AM by sgpm _ RSS

    Monarch pro v10 multilevel crosstab summary

    pm47 _

      Hi there,

       

      I am currently using Monarch 5. I wouldlike to know if version 10 allow to do the following :

       

      From :

       

      ID    TRANSACTION   VALUE1    VALUE2

      1          A         47         51

      2          A          3          4

      2          B          1         10

      /CODE

      I would like to obtain :

      ID     A_VALUE1    A_VALUE2   B_VALUE1   B_VALUE2

      1          47         51        null         null

      2           3          4          1           10

      /CODE

      that is, a 2-level crosstab summary.

       

      Please, tell me that it's possible!

       

      PM

        • Monarch pro v10 multilevel crosstab summary
          Olly Bond

          Hi PM,

           

          V10 allows you multiple measures for one across key field. But with a few calculated fields I don't think your output would be tricky to obtain.

           

          Define AVALUE1 as IF (TYPE=A; VALUE1; 0), etc for the others, and then you can have the measures as SUMS of the calculated fields.

           

          You can end up with zeroes where you might want nulls - depends how you prefer the data to appear, but you can fiddle with that in the Options > View settings.

           

          You might not even need v10 for this - not that I wouldn't encourage you to get it anyway for all the rest of the nice features from the last ten years

           

          Best wishes,

           

          Olly

            • Monarch pro v10 multilevel crosstab summary
              pm47 _

              Thank you for your response.

               

              Thing is, there are actually a lot of different "transaction" codes, so it would be painful to create all these calculated fields

               

              Aren't VALUE1 and VALUE2 2 different measures for the same TRANSACTION key? or do I misunderstand?

               

               

              PM

                • Monarch pro v10 multilevel crosstab summary
                  Olly Bond

                  Hi PM,

                   

                  You could try a different approach that's slightly less crude than making dozens of calculated fields.

                   

                  Treat the Value 1 and Value 2 columns as the detail of a two column multi column region, and the ID and Transaction columns as an append.

                   

                  I don't know when multi column regions were introduced - you might not have these in v5. Depending on the layout of the report, you might need access to Mod, Page, Line, Instr, and Lsplit functions - I only started with Monarch v7 so I can't say if you'll have all these.

                   

                  If you want to email me a sample of the report I'll happily mock something up for you.

                   

                  Best wishes,

                   

                  Olly

                  • Monarch pro v10 multilevel crosstab summary
                    Grant Perkins

                    Thank you for your response.

                     

                    Thing is, there are actually a lot of different "transaction" codes, so it would be painful to create all these calculated fields

                     

                    PM[/quote]

                     

                    If you have 'a lot' of transaction codes and, presumably, also a lot of values for each code how many columns would you expect to produce?

                     

                    I am guessing that the answer might be '2 x a lot' in which case I'm not entirely sure such a solution might be practical.

                     

                    Have I missed something?

                     

                    ETA: MCR (Multi Column Region) arrived with V7. Assuming just 2 value columns (or at the least a sensible number) it should probably work quite well for data extraction in this case.

                     

                     

                    Grant

                      • Monarch pro v10 multilevel crosstab summary
                        Olly Bond

                        Hi Grant,

                         

                        I suspect that 2 x a lot might be more than 250, and so you might be up against the limit of the number of fields.

                         

                        If my MCR approach wouldn't work, could an export to an Excel pivot table from v10 do the job? I don't have v10 here to play with.

                         

                        ATB,

                         

                        Olly

                          • Monarch pro v10 multilevel crosstab summary
                            Grant Perkins

                            Hi Grant,

                             

                            I suspect that 2 x a lot might be more than 250, and so you might be up against the limit of the number of fields.

                             

                            If my MCR approach wouldn't work, could an export to an Excel pivot table from v10 do the job? I don't have v10 here to play with.

                             

                            ATB,

                             

                            Olly[/quote]

                             

                            My thoughts too in respect of the number of columns.

                             

                            I don't consider myself to be any type of Excel/pivot table expert but I would have thought that V10 was likely to be a good solution - as you suggest. Perhaps Data Kruncher will comment later?

                             

                            However if your field identity requires the specific selection of Transaction and Value1 (or Trans and value2) then you still have a calculated field to create somewhere and thus are subject to the 255 field internal processing limit. Multiple models could get around that - or a multi-stage process following a single extraction to a table.

                             

                            However the calc of the combination field values would be a single field and dynamic for the report (Dynamic may or may not be a good thing of course ...). The new combined type/VAL field then becomes the key field for an Across display in the summary. This part should be quite easy top acheive with MCR assuming that the report format does not have any rare anomalies that complicate matters.

                             

                            HTH.

                             

                             

                            Grant

                              • Monarch pro v10 multilevel crosstab summary
                                Data Kruncher

                                Exporting to a pivot table may indeed be the only functional solution for the OP's needs, as described.

                                 

                                Fortunately, v10 exports pivot tables only to Excel 2007 file formats. I say fortunately because Excel 2007 and prior have about the same limitations in terms of the number of columns: 256. But Excel 2007 has 16,384 columns available. Hopefully they won't all be needed.

                                 

                                That should do it. I'm just glad that it won't be me who is reviewing that table.

                                 

                                Then again, "a lot" may only be 100 or so. That would still a fair bit of effort if the calculated field approach were to be undertaken.

                                 

                                HTH,

                                Kruncher