11 Replies Latest reply: Mar 2, 2015 10:02 AM by Grant Perkins RSS

    New to Monarch having issue with multiple line data

    Rachel Bedi

      I am brand new to Monarch (only using it a week!) and I am having a hard time modelling my data from a report. I think "cleared by" may help me but am not sure, if someone could help me out that would be FANTASTIC! I have three possible scenarios with the data;

       

      ***3010                                                                                                                            

      0638     LEGAL-STATUS-CODE         EQ                   0                                                                         

                              ASSIGN TO STATE:  B01          ASSIGN HOLD DAYS:          EVENT PROFILE:                                   

                              SECONDARY ROUTING:                                                                                         

      A**2900                                                                                                                            

                                                                                                   WRTDNGLR                              

                              ASSIGN TO STATE:               ASSIGN HOLD DAYS:          EVENT PROFILE:                                   

                              SECONDARY ROUTING:                                                                                         

      A**2920                                                                                                                            

                                                                                                    CHRGOFF        003                    

      7605     COF RECOMMEND DAT         RG        -00000000003    000000000000                                                         

                               ASSIGN TO STATE:               ASSIGN HOLD DAYS:          EVENT PROFILE:                                   

                               SECONDARY ROUTING:                                                                                         

       

      The pieces in orange I have set as my append templates. The items in blue are my detail records. Items in gray are my footers. The items in red are the ones I'm having a hard time with as each append record may or may not have this piece of data. Additionally as you can see each append record may or may not have the items in blue (my detail template).

       

      Could anyone help me out as to how to build this model? At this time I have it built well enough to include all my detail and footer information but I cannot seem to fit in the data in red in any way.

       

      Any help would be GREATLY appreciated!

        • Re: New to Monarch having issue with multiple line data
          Angelo Molina

          Hi Rachel,

           

          Have you tried to create a separate Append template for the Red one?

           

           

          Regards,

          Angelo

          • Re: New to Monarch having issue with multiple line data

            Rachel you are on the right path with the cleared by.    On each of the append templates make the clearing template the detail template.  This cause the append information to be only passed to the first detail record below it.  

            • Re: New to Monarch having issue with multiple line data
              Grant Perkins

              Rachel,

               

              Looking at your description and then at the information lines on the reports it's not totally clear what the REPORT is presenting as the DETAIL.

               

              Somewhere the report structure will have detail lines.  You have them as the blue lines but visually the Orange lines would seem more typical of a structured report format as a RECORD indicator.

               

              Each Orange record seems to have a corresponding Grey section which may or may not contain useful data. (In your example the first one does the other 2 do not.)

               

              The RED and BLUE sections look like information of status lines that may or may not appear. It would be unusual for the Blue lines to be thought of as DETAIL in a structured report.

               

              Is the report structure as posted how it actually appears if "printed" or has the format been corrupted by cut and paste?

               

              If we are modelling in Monarch and cannot find a repeating format somewhere in the report on which to base the extraction of data life becomes harder and the results much much less predictable - I would say probably unusable for anything that will be used on a regular basis. If you purposes is a one off data extraction (for loading data into a new system for example) it may just be OK to get the data any way you can but without a correctly identified structure to work with I would never rely on a model to give usable results a second time.

               

              Which piece if uniquely identifiable data on the report acts as a record identifier? Once we know that and we can work out how things attach to it we can take things from there.

               

               

              Grant

              • Re: New to Monarch having issue with multiple line data
                MelaSarenas _

                Hi Rachel,

                 

                Grant's right about the blue lines not suitable as the Detail line since it is missing in some instances.

                 

                If you could send us a copy of the report then maybe we can help out.  If there are sensitive data on the report you can mask it by using the redaction feature of Modeler.

                 

                Regards,

                Mela

                  • Re: New to Monarch having issue with multiple line data
                    Rachel Bedi

                    Good Morning Mela & Grant!

                     

                    Thank you both SO much for your help!

                     

                    I've attached a copy of the report in PDF format, if any explanation is needed please let me know, being so new to Monarch and not really having any in house resources to help me understand it I'm very grateful to have both of you helping me!  Essentially all the data that follows the rule number is what I need associated with the rule number.

                      • Re: New to Monarch having issue with multiple line data
                        MelaSarenas _

                        Ooops...sorry Rachel.  We could not see the file you attached. 

                         

                        Here is my email ermela_sarenas@datawatch.com.

                         

                        Regards,

                        Mela

                          • Re: New to Monarch having issue with multiple line data
                            MelaSarenas _

                            Sharing Rachel's report...couldn't figure out how to paste the text without ruining the alignment.

                             

                            At first glance this report seems easy to model.

                             

                            But then you have a section of the report below.

                            I suggested 2 models that combines the exports to a single file to capture all the information.

                              • Re: New to Monarch having issue with multiple line data
                                Grant Perkins

                                Thanks Mela and Rachel.

                                 

                                I have to ask firstly ... do any humans read this report? 

                                 

                                Secondly, are all the fields important to the analysis being undertaken? What are the RED fields? How does the information they contain relate to either DETAIL or an APPEND?

                                 

                                The problem is that I suspect you need to report all the records for a Rule Number even if a particular record has no ELEMENT. Correct?

                                 

                                So yes, two models may work (subject to any other funny stuff in the report that we have not yet spotted). One uses the (blue) Element lines for detail. The other uses the RED data and something to allow it to be matched into the missing  records that the main extraction will leave out. Then merge the two into a new file (maybe using a 3rd model if it would be easier than a lookup) and you should then be able to output whatever records you need. To to that I would work backwards from the required output so you don't end up doing work that will complicate things and not be required.

                                 

                                Is there is just a slight chance that the requirement for whatever needs to be delivered means that the records that would not have a Detail line (Blue) could be treated separately and so have no need to be merged back in with the rest.  If so it would useful to save the time and effort needed to make it possible to merge the outputs.

                                 

                                It's probably not that difficult but I'm all for keeping things as simple as possible whenever possible.

                                 

                                One heck of a challenge for week one Rachel! You have joined a long list of users new to Monarch who seem to have been presented with challenges that need some of the more advanced knowledge right from the start when they might hope to get a few weeks or months to gain more general understanding first. Why does that seem to happen so often?

                                 

                                Rachel, if you can tell us what the output needs to look like it should be possible to devise some details for the guidance for a good approach to the challenge.

                                 

                                 

                                Grant