8 Replies Latest reply: May 15, 2014 9:55 AM by StephenB _ RSS

    columns

    StephenB _

      I am new to Monarch, have version 7.  Is there  a special process that one should go through to ensure certain trapped data gets prosessed into the table as columns instead of rows I could sure benefit from any tip you would have.  I have read through the Monarch V7 Learning Guide and am still missing a concept and rationale; the more appends I make the more columns are produced. Thank you,  Stephen Butter

        • columns
          Grant Perkins

          Stephen,

           

          The simply way to think of this is that all of the FIELDS you map in your template will become COLUMNS.

           

          Your DETAIL TEMPLATE will appear as a ROW in the TABLE with a COLUMN for each FIELD defined.

           

          Any fields defined in APPEND, HEADER or FOOTER templates will be add to the row for the DETAIL record to which they are appended and will have their own columns in the table.

           

          The other aspect of COLUMNS in V7 is of course 'MULTI COLUMN REGIONS'(MCR) which is to do with the INPUT columns not the output columns.

           

          MCR can be thought of as a way of processing some narrow pages which have been placed side by side. Each page may have a sequence of one or more DETAIL records and possibly some appends in there as well.

           

          I get the impression that you may be struggling to work out exactly what should be in your DETAIL section. If you can post a sample of the report (or something that looks a bit like it) to the forum it may make it easier to help.

           

          You should be able to create a new post then click on the "CODE" button in the "Instant UBB Code" section of the input screen and PASTE your sample in between the two markers that will have appeared.

           

          I hope this helps in some way. If not perhaps it raises some more questions? In which case just post them and let's see what we can do.

           

          Grant

           

          Originally posted by StephenB:

          I am new to Monarch, have version 7.  Is there  a special process that one should go through to ensure certain trapped data gets prosessed into the table as columns instead of rows I could sure benefit from any tip you would have.  I have read through the Monarch V7 Learning Guide and am still missing a concept and rationale; the more appends I make the more columns are produced. Thank you,  Stephen Butter /b[/quote]

          • columns
            StephenB _

            /size[quote]code:[/size]1

            2    10

            3     7

            4    19

            5

            6

            7

            8    105

            9

            10

            11

            12

            13

            14

            15

            16

            17    34

             

             

             

             

             

             

            1    78

            2

            3    56

            4

            5

            6

            7    53

            8

            9

            10

            11

            12

            13

            14

            15

            16

            17[/QUOTE]

            • columns
              StephenB _

              Thank you Grant:

              The report I have to convert has as illustrated in the previous reply has 17 rows of data on page 1 and 17 rows of data on page 2.  My illustration shows 2 columns, but the actual reports have 8 columns of data.  The left most column (1-17) is the key (miles driven) or row number.  The other columns are the varied number of bus riders.  My problem is that I would like to get the page 1 and page 2 data (1-17 each)in different columns: 1-17 rows only in the finished table and 8 columns wide.  I have 296 school districts on this report, cutting and pasting is the only other method of moving the data, even with an Excel Macro (sometimes work, sometimes don't).  Stephen

              • columns
                Grant Perkins

                Stephen,

                 

                OK, I'm getting there.

                 

                So have you, in effect, got rows 1 to 17 with a total of 16 (?? 14?) 'counting' columns or do the columns (2 to 7?) on page 1 and page 2 need to be added together? (You say you want ot get them in different columns but that seems to be at odds with the final format you are seeking.)

                 

                Sometimes these things can be more difficult to put into words than to explain 'off the paper'.

                 

                What are the headings for the existing columns (from col 2 to the right)? Would it help to be able to refer to them?

                 

                Finally, why are there 2 pages to be combined? Are the pages actualy spearate and identifiable on the original report?

                 

                Grant

                 

                 

                Originally posted by StephenB:

                Thank you Grant:

                The report I have to convert has as illustrated in the previous reply has 17 rows of data on page 1 and 17 rows of data on page 2.  My illustration shows 2 columns, but the actual reports have 8 columns of data.  The left most column (1-17) is the key (miles driven) or row number.  The other columns are the varied number of bus riders.  My problem is that I would like to get the page 1 and page 2 data (1-17 each)in different columns: 1-17 rows only in the finished table and 8 columns wide.  I have 296 school districts on this report, cutting and pasting is the only other method of moving the data, even with an Excel Macro (sometimes work, sometimes don't).  Stephen /b[/quote]

                • columns
                  Bob Yarnall

                  Perhaps Excel would be easier than Monarch.  Simply import the report into Excel then transpose the data from rows to columns.  You can use the macro recorder to save your steps then run it to do the work for you.  Here is a section of code to give you an idea of how it works.

                   

                  [font="courier"]    Sheets("Sheet1").Select

                      Range("A1").Select

                      Range(Selection, Selection.End(xlDown)).Select

                      Selection.Copy

                      Sheets("Sheet2").Select

                      Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

                      Application.CutCopyMode = False

                      Sheets("Sheet1").Select /font[/quote]

                  • columns
                    StephenB _

                    Grant:

                    Yes, 17 rows with 4 columns plus the 1-17 column which is the miles for a total of 5 columns in the report.  Page 1 of the report shows the 5 columns with headings of Miles, A, I, P, and B.  Page 2 of the report shows the 5 columns with headings of Miles, S, X, Y, Z.  Sorry for not being clear in the prior explanation.  What the end product has to be is 17 rows with 9 columns: Miles, A, I, P, B, S, X, Y, Z.  When I trap the above data in the template, both page 1 and page 2 1-17 rows for the Miles load in sequence in the table: page 1 1-17 in the first column followed by page 2 1-17 directly following in the first column.  Stephen

                    • columns
                      Grant Perkins

                      Stephen,

                       

                      OK, I have it now. I figured it would be something like that but having the complete picture makes it easier to explain (I hope! Set a value and see of the red lines fit OK. If not, change the value(s) until they do.

                       

                      Taking your page 1, map the A, I, P and B columns as follows.

                       

                      Highlight one of the rows with data in at least the first column. For the template you may be able to use a trap for a numeric value in the 'Miles' position at the start of the row since there will always be a numeric value there - probably in the second character position.

                       

                      'Paint' the field for the qty field under your active column for 'A'. The appropriate fields for columns I, P and B should also now appear as highlights as will the 4 columns on page 2.

                       

                      That is your detail template. OK it.

                       

                      Now highlight the 'heading line on page 1 - column headings 'Miles'   'A'    'I'     'P'     'B'

                       

                       

                      and create a new template (this will be an append template).

                       

                      The trap will probably be 'Miles' where that heading appears on the row.  Paint a field where the column heading for the first column  - "A" appears. You should now see the headings of the other columns also appear in highlight.

                       

                      OK that template. One more to go.

                       

                      Add the last append template to capture the values for the 'MILES' column. My guess is that you can use the same trap as for the DETAIL template but paint the field for just the first 2 character columns.

                       

                      You should now be able to see a table that gives the information you require but not the grouping.

                       

                      Next step is to create a SUMMARY.

                       

                      Make the summary with the following fields from your records.

                       

                      Key Fields

                       

                      1. Miles column values

                       

                      2. Your column Heading append fields (A,I, etc.)

                       

                      With this field in the Key values window, right click on the field to get to the properties screen. Click on the DISPLAY tab. Set the 'Display Values' to be ACROSS.

                       

                      3. Your value fields from your A, I, etc. columns.

                       

                      For the MEASURE use the value fields again. This will default to a SUM calculation assuming that the original field has been defined as numeric which I would imagine it has.

                       

                      You will probably see several rows with occurrences of the values 1 to 17 from the MILES column. Fear not - that's OK for now. You should also see your A, I, etc. columns displaying across the screen.

                       

                      One more thing to do.

                       

                      If you now 'drill up' a level in the summary (button in the menu bar with an up pointing red arrow) you should now see just your 17 rows of MILEAGES and the values in the cells for each of the 8 columns for each row should give you the SUM of passenger numbers for the entire report.

                       

                      You could also have some fun playing around with some other measures.

                       

                      You also mentioned that you have the information for several hundred school districts. Now if the district (or indeed any other grouping or sub-grouping) is available on the report somewhere you could include that information in the table and use it as a summary level in the summaries to provide all sorts of possible analysis outputs from a single report mining process.

                       

                      But let's take one step at a time for now. So see how that works for you - there may yet be some complications in the report that mean we need to use a few other features to extract a clean data set. We can look at the rest of the requirement after that.

                       

                      I hope this has helped so far.

                       

                      Grant

                       

                       

                        Originally posted by StephenB:

                      Grant:

                      Yes, 17 rows with 4 columns plus the 1-17 column which is the miles for a total of 5 columns in the report.  Page 1 of the report shows the 5 columns with headings of Miles, A, I, P, and B.  Page 2 of the report shows the 5 columns with headings of Miles, S, X, Y, Z.  Sorry for not being clear in the prior explanation.  What the end product has to be is 17 rows with 9 columns: Miles, A, I, P, B, S, X, Y, Z.  When I trap the above data in the template, both page 1 and page 2 1-17 rows for the Miles load in sequence in the table: page 1 1-17 in the first column followed by page 2 1-17 directly following in the first column.  Stephen /b[/quote]

                       

                      [size="1"][ June 24, 2004, 08:23 PM: Message edited by: Grant Perkins ][/size]

                      • columns
                        StephenB _

                        Grant:

                        Thanks a million!

                        Stephen