6 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Detail information on more than one row for a given record.

    heathy _

      Hi,

       

      I'm new to Monarch and have a question....

       

      I have data in the following format

       

      [font="courier"]1     OUTPULSE     0049403501;     

      1     ECHO          2;     

      1     NPI          UNKNOWN      UNKNOWN ;

      1     END;          

                      

                      

      2     OUTPULSE     00494035010;     

      2     NPI          UNKNOWN      UNKNOWN ;

      2     END;          

                      

                      

      3     OUTPULSE     97200;     

      3     ECHO          6;     

      3     NPI          UNKNOWN      UNKNOWN ;

      3     END;          

                      

                      

      4     OUTPULSE     9720091;     

      4     NPI          UNKNOWN      UNKNOWN ;

      4     END;          /font[/quote]For the above sample I would want 4 separate records. 

       

      As you can see the number of rows is not consistant for each record (limited to between 1-5 though).  The last row of a record will always contain "END;" in the 2nd field.

       

      Each row can only have a maximum of 4 values including the number in the 1st column.

       

      What I tried was creating a detail template that covered 5 rows and I defined fields to capture all of the 4 fields in each of the 5 rows.  The problem is that (for example) if one record has only 2 rows then since I'm capturing 5 rows I get some of the next record

       

      I need to somehow terminate the record when I see "END;".

       

      Any ideas?

       

      Cheers Ian

        • Detail information on more than one row for a given record.
          Grant Perkins

          Hi Ian and welcome.

           

          WOuld it be fair to assume that the records have a minimum of 3 rows?

           

          Did you choose 5 rows for the template sample because some records may have 5 rows or is the max 4?

           

          Is the second column (OUTPULSE, etc) data or a 'fixed' label describing the columns which follow. By 'fixed' I mean that there are a limited number of values and the text itself is not important except as an identifier of what the 2 subsequent columns are recording.

           

          It does look a little tricky, especially if it comes early in your exposure to Monarch. There's nothing like diving in at the deep end!    

           

          Grant

          • Detail information on more than one row for a given record.
            heathy _

            Hi,

             

            Unfortunately the only consistent information for a given record (spread across multiple rows) is having the same number as the 1st entry.

             

            The entries "OUTPULSE", "ECHO", "NPI" etc. can appear in any order or not at all.  Also as you can see these different commands have a different number of parameters; i.e. OUTPULSE has 1 additional value whilst NPI has 2 additional values.

             

            I've checked through the whole of my input file and the maximum number of rows for a given record is 5 (the last one having "END;").

             

            I set up an detail template that covered 5 rows and had 16 fields.

             

            1 for the 1st number on the 1st row (no need to get the same number from the other rows).

             

            Then 5 x 3 fields for the 5 rows with the 3 entries.

             

            The trouble is that if I have a record that has only 2 rows it still takes 5 rows, so it takes some of the next record.

             

            The only thought I just had was to write a macro in Excel (this is the original format my input data is in) to add say 5 blank rows between each record to stop this happening.

             

            Cheers Ian

            • Detail information on more than one row for a given record.
              heathy _

              Sorry didn't answer your question.

               

              The entries OUTPULSE, ECHO, NPI are actually commands (this file is a part of a configuration script from a PBX).

               

              The OUTPULSE command has 1 parameter

              The ECHO command has 1 parameter

              The NPI command has 2 parameters

               

              Cheers Ian

              • Detail information on more than one row for a given record.
                Grant Perkins

                Originally posted by heathy:

                Sorry didn't answer your question.

                 

                The entries OUTPULSE, ECHO, NPI are actually commands (this file is a part of a configuration script from a PBX).

                 

                The OUTPULSE command has 1 parameter

                The ECHO command has 1 parameter

                The NPI command has 2 parameters

                 

                Cheers Ian /b[/quote]So if we donlt trap the OUTPULSE, ECHO and NPI fields but name the parameters for them in cols 3 and 4 (where appropriate) that would give you the result you require?  I assume the 'END' has no real purpose for you. Just an end of record marker?

                 

                Grant

                • Detail information on more than one row for a given record.
                  heathy _

                  i still need those parameters, but it's not a problem to capture them anyway.

                   

                  it's the variable number of rows that's the issue.

                   

                  cheers ian

                  • Detail information on more than one row for a given record.
                    Grant Perkins

                    Originally posted by heathy:

                    i still need those parameters, but it's not a problem to capture them anyway.

                     

                    it's the variable number of rows that's the issue.

                     

                    cheers ian /b[/quote]OK, let me take my thoughts a stage further.

                     

                    If you don't need the "END" line (it always exists) you could work with a 4 line template sample so the 3 linse which are the minimum plus a blank line below a 3 line record.

                     

                    No overlap there.

                     

                    Map your column 1 field in row 1

                     

                    Map a field in row 1 for the widest possibly positions (of any field on any row )in column 3.

                     

                    Name it for one of the text strings that might appear in column 2 - OUTPULSE for example. Go to the advanced properties for that field and in the "Start Field On" parameter use the "Preceding string" option and enter "outpulse" as the preceding string.

                     

                    Repeat this on the next line for one of the other text strings that might appear in col 2.

                     

                    For the NPI string you will also need a field in col 4. You can use the same preceding string but obviously will need a different field name.

                     

                    The order does not matter. The NPI definitions don't have to be on the same line but it may make maintenance easier in the future if they are  consistent.

                     

                    I have assumed that you can trap the records accurately without problems.

                     

                    See if that works for you. I am slightly concerned that you included an "etc" in there at one point. If the number of potential parameters in col 2 exceeds 4 the success of this approach will be limited. On the other hand if there were many options but you only wanted to see and report 4 of the parameters doing this would work as a great filter!

                     

                    If you can accept an ever present "END;" then you smallest record woould still allow 4 fixed fields to be defined in col 2 but the values assigned would vary as you described above, The preceding string fields would still work but there may not be enough of them. Introducing extra blank lines - as you suggested previously - could be useful to extend the number of rows available and therefore the number of parameter variable strings for which fields could be set.

                     

                    HTH.

                     

                    Grant