9 Replies Latest reply: May 15, 2014 10:13 AM by Olly Bond RSS

    Display Null for Missing Records in File

    Matixsnow _

      I want to scrape the following data:

      201

      501

       

      201

      501

       

      201 (this 201 record is missing a corresponding 501 record)

       

      201

      501

       

      -I cannot figure out how to get Monarch to capture the 201 with the missing 501 record.

       

      -I can only get Monarch to display this 201 record with the 501 record from the previous 201 record.

       

      -I want Monarch display Null for the 501 record that is missing.  Any thoughts?

        • Display Null for Missing Records in File
          Olly Bond

          Hello Matixsnow

           

          If you define a two line sample for a template trapping on the 201, and define a field on line 1 for the 201 and on line 2 for the 501, then as long as the second field is Numeric then Monarch should show it as (Null).

           

          Hope this helps,

           

          Olly

            • Display Null for Missing Records in File
              Matixsnow _

              Thanks for the response.  The problem is that there are records between the records that I want to capture.  The records between are not consistent in the file.  I can't get Monarch to disregard this.

               

              This is what I mean (bold values I would like to capture):

              -


              201[/B]

              303

              (501 does not exist, but display as null)

              -


              201[/B]

              501[/B]

              -


              201[/B]

              501[/B]

              -


              201[/B]

              303

              (501 does not exist, but display as null)

              -


              201[/B]

              501[/B]

              -


              201[/B]

              303

              404

              501[/B]

              -


               

                • Display Null for Missing Records in File
                  Olly Bond

                  Hello Matixsnow,

                   

                  Thanks for clarifying - BTW you can post fragments of reports between CODE tags which can help others suggest ideas for the right approach.

                   

                  For this case, as long as you have v10 or above, you should be fine by using two single line templates - a detail on the 201 records and a footer on the 501 records. Set the footer to be Cleared By the next occurence of the Detail, and you should be fine.

                   

                  Best wishes,

                   

                  Olly

                    • Display Null for Missing Records in File
                      Matixsnow _

                      I have v8.

                       

                      I setup detail and footer, but how do I do this?

                       

                      'Set the footer to be Cleared By the next occurence of the Detail, and you should be fine.'

                        • Display Null for Missing Records in File
                          Olly Bond

                          Hello Matixsnow,

                           

                          "as long as you have v10 or above" - the Cleared By function only arrived in v10.

                           

                          I got an email today from Datawatch's UK office saying they are offering upgrades to v11 for users of older versions until the end of June, but I don't have the details to hand.

                           

                          I'd have thought that an upgrade would be the easiest way forward.

                           

                          Best wishes,

                           

                          Olly

                            • Display Null for Missing Records in File
                              Matixsnow _

                              Thanks for the info.

                               

                              So there is no way of achieving my results with out a newer version?

                               

                              It seems that what I am trying to do is fairly basic...

                                • Display Null for Missing Records in File
                                  Olly Bond

                                  Hello Matixsnow,

                                   

                                  You can handle the problem in earlier versions in two ways. There are threads here on the "missing detail" problem, and on the "guru trap" which might give you helpful pointers.

                                   

                                  Best wishes,

                                   

                                  Olly

                                    • Display Null for Missing Records in File
                                      Matixsnow _

                                      Hey,

                                       

                                      I saw your posts on Guru Trap and was able to get this to work.

                                       

                                      Thanks!

                                        • Display Null for Missing Records in File
                                          Olly Bond

                                          Hello Matixsnow,

                                           

                                          The guru trap works for intermittent footers like your case, but I wouldn't want to try to handle anything with more than two templates that way. The other method is to build multiple models and pass the data through Monarch more than once - easy enough if you don't mind using a batch file, but a little bit clunky and you don't get any logging like you would with DataPump, and multi-pass solutions aren't so easy to deliver in a browser based version of Monarch Server.

                                           

                                          I don't work for Datawatch, nor do I own any shares in the company, nor do I resell Monarch, so I'm not on a cut for any of this, but I am constantly surprised here on the forums at the number of users stuck on old versions. It's fine to keep running v8 if you're all still running XP and nobody wants Office 2010 support, or better PDF and XPS handling, but things like the Cleared By feature and the fix to MCR are so helpful just for general model building, that keeping the software under maintenance so you can upgrade when you need the features seems like a bargain.

                                           

                                          Best wishes,

                                           

                                          Olly