8 Replies Latest reply: May 26, 2017 5:31 AM by Grant Perkins RSS

    Multiple overlapping detail lines

    Billy Mackie

                 1           2        3        4        5        6        7        8        9       10       11       12       13       14       15       16       17       18       19       20       21       22

      A: FJHF00XX1 L          HRHRD Compan 18/10/11 84      10     9.45                                                11100.00   11100.00 11/111/11 11/11/11                     

      B:     11565.00    11196.18     0.00                                                                11101.56                                   

      C:     11151.30     1113.61 11157.69     0.00                                                                                                                                                                 

      D:     11101.56    11101.56                                                                                                                                                                 

      E:      1119.63

       

       

      A: FJHF00XX1 L          HRHRD Compan 18/10/11 84      10     9.45                                                11100.00   11100.00 11/111/11 11/11/11                     

      B:     11565.00    11196.18     0.00                                                                11101.56                                   

      C:     11151.30     1113.61 11157.69     0.00                                                                                                                                                                 

      D:     11101.56    11101.56                                                                                                                                                                 

      E:      1119.63  

       

       

      Each A1 to E22 is an individual record, I have been able to grab the data by specifying the A: line as the detail and B to E: as footers which means I have a very long row for each record/

       

      How can I get the data into excel as it appears on the report? Multi line is not working as rows over lap with above and below on some records? Many thanks

        • Re: Multiple overlapping detail lines
          Chris Porthouse

          What format is the data in to start with (Excel, CSV, Delimited Text, PDF, etc.)?  There are a couple of things you can try but it depends on the format the source data you are using to model.

          • Re: Multiple overlapping detail lines
            Austin Perkins

            Also, can you tell us what version of Monarch you are using? This could impact the answer you might receive.

            • Re: Multiple overlapping detail lines
              Chris Porthouse

              This response should be true of at least v13 or better of Monarch Classic.  I am also making an assumption that each record contains 5 rows.  I also removed the header and column info (A-E, 1-22), but it shouldn't matter for this discussion.

              Select 5 lines from the line selection window and create a new detail template:

              Once you set an appropriate trap line, you can select mulitple fields on different lines.  You cannot "draw" a field to contain multiple lines, but you can you some advance template techniques to get past that problem.  If your data is not consistently spaced where you can draw the fields you want to capture, another possibility would be to create 5 long fields (1 for each row) and then use formula fields to break them down to their individual records.

               

              Keep in mind you are limited to 512 fields.  I know this is not too specific but I hope it points you in the right direction.  If you need more info or clarification let me know.  Another good resource is the built in help file for monarch:

                • Re: Multiple overlapping detail lines
                  Billy Mackie

                  I cant seem to select multiple lines, is that because I am on version 11?

                    • Re: Multiple overlapping detail lines
                      Grant Perkins

                      No, multi row templates have been around since the very early days.

                       

                      What are the 11/111/11 entries in your dummy data representing?

                       

                      Are all of the columns individual or do they go in repeating groups?

                       

                      Are the real "detail" records (in Monarch terms) the A rows or the B,C,D,E rows as single rows?

                       

                      In other words, if your report file has Column Titles as a header line above the data rows does it have one row of column headers or 4/5 separate rows each with different column names?

                        • Re: Multiple overlapping detail lines
                          Billy Mackie

                          11/111/11 typo, it is a data  dd/mm/yy

                          All rows are detail A to E: There is a key at the top of the repor

                          I was able to trap all fields in one template as suggested but I still ended up with one really long row a1 a2  ... e21 e22 (which is actually okay as each row is one complete record) but I would still like to get it as per the report like below

                           

                           

                          a1 .... a22

                          b1... b22

                          ...

                          e1... e22

                          a1....a22

                          etc

                            • Re: Multiple overlapping detail lines
                              Grant Perkins

                              Billy, I think we probably would benefit from knowing the ultimate purpose for the extracted data.

                               

                              Based on the information above each line needs to be treated as a detail line - single row.

                               

                              However your A: row, or at least part of it, is completely different at the start and is more like a record header.

                               

                              So are you just seeking to replicate the existing report to another report with exactly the same layout?

                               

                              Or to dump it into Excel with the same layout?

                               

                              Nothing more?

                               

                              Clearly the first line will need different field columns to the rest of the report and, having guessed that the 11/111/11 field might be dates, are the fields in each column in each row (other than the row one anomaly) all of the same data type? If not, will extracting everything into Character based fields (i.e. TEXT) be suitable?

                               

                              Is the .txt file just text in columns or is it a csv format?

                               

                              As a guess I wonder if this is actually a single row record that has been wrapped into multiple rows to fit into a standard width "paper" report. It looks a bit strange, based on how it looks after posting   - which might not be accurate - compared to most report sources.

                               

                              EDIT:  What I really meant there is could it be a former csv type file that have been re-presented as a fixed width format and then wrapped to a particular line width.

                               

                              Grant