16 Replies Latest reply: Dec 17, 2014 12:24 PM by John Krstich RSS

    Monarch 11- Populating Null values with Pervious NON-Blank Value

    Datla Ranga Raju


      Hi,

        I am using Monarch 11. while creating a Model, I tried to populate Null values in one of the column(say column B) with the previous non-Null  value in the column. Earlier when I used Monarch 8 ,I remember using such functionality. Can anyone please guide me in resolving this issue?

       

      Thanks,

      Raju

        • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
          Olly Bond

          Hello Raju,

           

          If you are working from a report source, then you can specify this in the Advanced properties tab of the field definition. The option is called "copy value from previous record".

           

          If you're working from a database, things are a little more tricky, and might need two models.

           

          Best wishes,

           

          Olly

            • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
              Datla Ranga Raju

              Hi Olly,

                 Thanks for your reply!!!

                 Yes, I am working on database. And can you please let me know how we can proceed further by creating two models?

               

               

              Thanks,

              Raju.


              • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                John Krstich

                Hi Olly,  I'm actually having an opposite problem.  I'm getting trapped data 'bleeding' from a previous record populating a null.  So I want to trap the data when it happens but after that first occurrence it repeats or bleeds into the next record where it really does not exist.  I need from the Advanced Template Field Properties Empty Cells:  Do not copy from previous records (or something like that).

                 

                Monarch Modeler amazing product but I don't understand why its picking up data from the previous record when Detail template is specific to exclude that record (page in this instance).  Source: Text file   Data: Letters

                Thanks in advance,

                jk

                  • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                    Olly Bond

                    Hi John,

                     

                    This is easy enough if you define your templates properly - you need to use the Cleared By function so that Appends are Cleared By the relevant Detail. For tricky data, there are hacks like making a blank template or fiddling with Page Headers. But generally Appends will cascade all the way down the table, unless another instance of the Append trap is encountered, or you tell Modeler to clear the Append after the relevant stop point.

                     

                    Best wishes,

                     

                    Olly

                      • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                        John Krstich

                        Olly,

                         

                        Thanks for your patience - I'm self taught Monarch user.  Where is this Cleared By function applied regarding my Append Template?  The field that's bleeding unwanted data into the next record. 

                         

                        I'm in Table Design where it lists my model fields.  I highlight the Append trap in question (good so far?)  then Functions?  I saw Filter looks like it gets me to the same screen.

                         

                        This 'inconsistency' in data extract has been driving me batty. 

                         

                        Thank you for your quick response on a Friday afternoon in the middle of Dec.  I just took a chance at this I can't believe I'm actually getting a response! 

                         

                        jk

                          • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                            Olly Bond

                            Hi John,

                             

                            It's in Report Design, when you edit the Template.

                             

                            Best wishes

                             

                            Olly

                              • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                                John Krstich

                                Hi Olly,

                                 

                                Screen shot of the location you're referring to, correct?  If correct, this is no help.  I think I'm not explaining my problem situation not very well.

                                 

                                I've attached an export of the Table, Excel file.  (changed the data for disclaimer reasons, nothing personal). 

                                Source is Text file: Monarch Model is extracting a Letter File which holds about a dozen different clients - credit card issuers.

                                Detail line:  Grabs a specific client which is found at the bottom of each letter page (and I've got several appends including the problem one). 

                                Different clients have different legal requirements they impose on us.

                                 

                                Excel: Column Q is the problem field.   You'll see row 317 www.xxx-trans.com   The specific client in this report does not allow for this verbiage in their letters but the letter immediately preceding this one does belongs to a client that does allow the verbiage.  So when you go to the report and look up this record, www.xxx-trans.com is not in the body of the letter.  It's somehow adding it to the extract from the previous record. 

                                 

                                You may be asking 'then why are you even bothering to trap www.xxx-trans.com if client XYZ insists it not be present'. Good question.

                                Because we have a 3rd party vender doing all our letters and we don't trust their programming.  We have found, by manually search of the letter proofs, by accident, that www.xxx-trans.com was accidently, inconsistently added to a letter here and there by accident when it shouldn't have been.  And that's why we bought Monarch Modeler.

                                 

                                So, I have all these letters in text files with about a dozen clients mixed in pulling one client at a time to verify their specific rules settings.  Somehow the other clients data is 'bleeding' to this client's extract for this field.  I would say that probably most of the time this field will come back with a 'null' but we're looking for that rare time when an error has occurred with our vender.  We're talking hundreds of thousands of letters per month and we can't afford any mistakes to go out to our customers.   Can this work?   We bought 12.0.5.2002 version.

                                 

                                Thanks,

                                jk

                                • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                                  John Krstich

                                  Hi Olly,

                                   

                                  Hope your weekend is good.  Say, any updates, clues as to why that append field is picking up data from the previous letter (bleeding through)?  My detail trap specifically excludes that product/client from the table extract.    

                                   

                                  Thanks,

                                  jk

                                    • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                                      Olly Bond

                                      Hello John,

                                       

                                      In Report Design, edit the Append Template so that it is Cleared By the Detail template. Then you should be able to see the difference in the Table.

                                       

                                      Best wishes,

                                       

                                      Olly

                                        • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                                          John Krstich

                                          Success!!  Olly you are the MAN!!! 

                                           

                                          However, I discovered another problem:  My Detail trap is at the footer of the letter page (in-house detail info at the bottom of each letter, kind of a stamp if you will).  For a while I couldn't figure out why it wasn't working 100%.  Well, what's happening is whenever there are letters from other clients in-between, who accept the www.xxx-collect.com string, it rolls over into the first occurrence of the previous letter until it hits the bottom of that page where the detail is located and picks it up after-the-fact.  By then its too late and has already grabbed it.   If that makes sense...  I'm starting to 'think' in the way the model works.  Not sure how smart that is yet?

                                           

                                          So either work on changing the detail trap above the body of the letter or you know a better way by using the footer/detail combo somehow. 

                                          Sorry, I'm a real pain in the neck.  What would you recommend?  Not sure how flexible Monarch is with that?

                                           

                                          Thanks,

                                          jk

                                            • Re: Monarch 11- Populating Null values with Pervious NON-Blank Value
                                              Olly Bond

                                              Hello John,

                                               

                                              If you've got a multi-line detail template, then you can avoid interruptions in it by trapping these as a Page Header template - a special kind of Append. I think that's in one of the Lesson.xmod tutorial files but I can't remember which, but if you experiment you'll get the idea.

                                               

                                              Generally the Detail template should be used for the item that occurs most frequently, or for each instance the data that will go to make one row of your table. There are tricks, exceptions, and special cases.

                                               

                                              Monarch Experts offer a next working day service to solve any model building challenge, but they do charge for that.

                                               

                                              Best wishes,

                                               

                                              Olly