5 Replies Latest reply: May 15, 2014 10:15 AM by Grant Perkins RSS

    Blank Fields ?

    Jeff T

      Hello, I am new to Monarch, Very New.

      I am trying to scrape data from a report.

      This report has a 1 detail row with 6 fields of data for every record.

      Some records have 2-12 additional rows of data where 5 of these fields are duplicated with different data, based on the occurance of additional transactions.

      I have a Detail template setup that pulls the first detail row perfectly.

       

      I created an Append template to pull the other row(s).

      The trap works just as it should, trapping the correct rows and fields.

      The data that is returned from this append template is NOT correct.

      It will not return blanks if these Additional rows of data do not exist for a specific record/account. Instead it returns the data that was pulled into those fields for the last record that had those records.

       

      How Can I make those fields be blank/null if they do not exist for this Account.

      Below is a sample set of data.

       

        0103327111                         01/03/13   02/01/13             19.38   072  1MP8   __________________________________________

                                                        TOTAL              19.38                                                         

        0103338222                         12/31/12   01/01/13          1,800.69   072  1MP8   __________________________________________

                                                        TOTAL           1,800.69                                                         

        0103355333                         12/20/12   02/01/13            800.00   072  0MP3   __________________________________________

                                                        TOTAL             800.00                                                         

        0103387444                         12/21/12   12/01/12              7.95-  073         __________________________________________

                                           12/03/12   12/01/12            394.48   073  1901   __________________________________________

                                                        TOTAL             386.53                                                         

        0103488555                         01/03/13   02/01/13            600.00   072  1MP8   __________________________________________

                                                        TOTAL             600.00                                                         

        0103504666                         12/21/12   01/01/13          1,150.00   072  1MP8   __________________________________________

                                                        TOTAL           1,150.00                                                         

        0103526777                         12/31/12   01/01/13          1,142.88   072  1MP8   __________________________________________

                                           12/02/12   12/02/12            394.48   073  1901   __________________________________________

                                                        TOTAL           1,142.88                                                         

        0103528888                         12/04/12   01/01/13               .75   073  1012   __________________________________________

                                                        TOTAL                .75                                                         

        0103535999                         12/31/12   01/01/13          2,000.00   072  1MP8   __________________________________________

                                           12/05/12   12/05/12            394.48   073  1901   __________________________________________

                                                        TOTAL           2,000.00                                                         

        0103544000                         01/03/13   01/01/13            810.00   072  1MP8   __________________________________________

                                                        TOTAL             810.00                                                         

        0103556112                         12/26/12   01/01/13          1,376.47   072  1MP8   __________________________________________

                                                        TOTAL           1,376.47

      /code

        • Blank Fields ?
          Grant Perkins

          Hi Jeff and welcome to the world of Monarch and in particular this forum.

           

          The basic concept at an APPEND template if to attach information that appear BEFORE a detail line on a report to each detail line to which it applies. There are ways to "adapt" this functionality to make use fo information that happens to appear AFTER the detail line(s) but I'm not sure that will be usefully applicable in this case.

           

          Similarly, to capture data that appears AFTER the detail line(s) and connect it with each detail record Monarch offers FOOTER templates but that doe not really apply here either.

           

          Classic Monarch would suggest that each report line with 2 dates is a DETAIL template candidate and the column on the extreme left of the report is an Append template field candidate. This is very close to what you have done, I suspect, just a different view of what fits where and in which Template.

           

          However the reality is that how you are best advised to extract depends on what you need the output to look like and what purpose it will have after extraction.

           

          An alternative apporach here would have each record set up as you have done - 6 fields presumably trapped on the first column? - and then make the middle columns multirow selections, probably all 5 columns in a single field, with a variable number of rows since there can be up to 12 (maybe more?). You might then split each row into 5 separate fields once more creating a field for each column in each row giving 60 fields plus one field for the first column in each record. (Or more if there might sometimes be even more than 12 rows.

           

          Which approach is best for your needs rather depends on what you want to get from the results.

           

          The second option probably involved things that you have not yet come across in Monarch so I wont attempt to describe them in detail now but will be happy to take you through what is required if that approach is the best solution for you. Having some information about what you need from the output will be a great help to make that decision.

           

          HTH.

           

           

          Grant

            • Blank Fields ?
              Jeff T

              Grant,

              Thank you for replying.

              Based on your response I have re-created my templates scraping the 5 fields in the detail and the left column in the append and it appears to work. I am doing data validation now.

              THANK YOU FOR YOUR HELP !

                • Blank Fields ?
                  Grant Perkins

                  Hi Jeff,

                   

                  Should work fine (of course!    ) - does it give you the results in the way that you need the information?

                   

                  It you are extracting for onward transmission to some other system it is likely you have what you need.

                   

                  If you are extracting for further analysis then check out the possibilities in Monarch Summaries.

                   

                  If the results are fine but you can think of other ways of presenting them that don't seem to be covered so far - tell us about them and wait for further suggestions!

                   

                  (Actually no, don't wait. Tell us and start playing around  - it's a great way to come up with ideas about how you want to do things and how you might make it happen!)

                   

                   

                  Grant

                    • Blank Fields ?
                      Jeff T

                      Hi Jeff,

                       

                      ...- does it give you the results in the way that you need the information?

                       

                      It you are extracting for onward transmission to some other system it is likely you have what you need.

                       

                      Grant[/QUOTE]

                       

                      Yes it worked great, exported to a | delimited file.

                      That is exactly what we are trying to do. Scrape these reports(there are several) and place the data in a database to be used for data migration between systems.

                      Thank you again for your help!

                        • Blank Fields ?
                          Grant Perkins

                          You're welcome Jeff.

                           

                          Don't forget that, should you need it, Monarch has a lot of powerful options for creating calculated fields. That can often be incredibly useful when converting data between systems.

                           

                          The obvious thing is populating the new system data.

                           

                          The less obvious thing is creating a readable, non-proprietory 'backup' of everything in text files (or whatever ...).

                           

                          The even less obvious thing is saving stuff that has no place to go in the new system so that is can still be retrieved and used should it be required. Maybe even integrated with reporting requirements from the new system.

                           

                          The only time I can think that this might not be desirable is if the reason for the new system is to ensure some of the old data never again see the light of day ...   

                           

                           

                          Grant