3 Replies Latest reply: Mar 25, 2015 3:41 PM by Grant Perkins RSS

    Variable Number of data lines per record

    ricklesgolf _

      I am trying to extract data from a report that has a one line detail line but has variable lines of data.  I would like the output to create a record for each variable line.  I hope my explanation makes sense.  Here is an example:

       

      CLP-Jim Smith

      SVC*HC 99211

      DTM*20141121

      REF*287654

      SVC*HC 98386

      DTM*20150116

      REF*390057

      LQ*1977

      SVC*HC 77892

      DTM*20150101

      REF*976321

      CLP-Jane Doe

       

      CLP is the only consistent field in the data and I presume it would be the detail.  CLP identifies a new record.  I need anything that has SVC*HC.  There could be 1 or 20 different SVC*HC types for each record.  I also need the associated DTM*, REF* fields that falls below each SVC*HC.

      For the first record, I need the following:

       

       

      CLPSVCDTM
      Jim Smith9921120141121
      Jim Smith9838620150116
      Jim Smith7789220150101
        • Re: Variable Number of data lines per record
          Grant Perkins

          Hi,

           

          Based on the row samples you have posted (and I am aware it may not be quite that basic) I have 2 questions.

           

           

           

          Does the LQ* line (or any others) matter to you if they should appear?

           

          Can you ever get any CLP line that have no SVC lines under them but that need to be reported?

           

           

           

          If the answer to both of those questions is "No" then you should probably make the SVC lines your detail template line in a 3 line sample to get the DTM and REF fields as well.

           

          The CLP lines would then be set to Appends which, once the columns in the table have been re-ordered if necessary, should give what you describe.

           

          If there is a little more to the total requirement then we may need to add some extra processing or extra steps to the process to give the complete result you need.

           

          HTH.

           

           

          Grant

            • Re: Variable Number of data lines per record
              ricklesgolf _

              Thanks so much.  It is amazing how creative you can be with these templates to get the job done.

                • Re: Variable Number of data lines per record
                  Grant Perkins

                  Hi Rick,

                   

                  Glad that worked for you.

                   

                  Keep on experimenting if you get any seemingly tricky reports. There is a lot you should be able to do that may not be obvious at first sight. The thing is that attempting to document some of the less obvious approaches will always seem puzzling and meaningless until you perhaps find yourself needing them.  Only then does the relevance become apparent.

                   

                  Back in time there was a Monarch Newsletter that was always interesting for regular users and invariably offered and example of something useful that might come in handy in the future or suggest a way to get more out of Monarch in ways that existing users may not have previously considered.

                   

                  Even more interesting were the Conferences and the handout CDs from the presentations. It was one of those that opened my eyes to the greater-than-obvious potential of Monarch functionality with worked examples of some really obscure and complex report mining. Suddenly things that I had assumed were so obscure that they could never be possible became not just possible but commonly used "tools" in my Monarch armoury (and then shared in the forums).

                   

                  Have fun with Monarch (or Modeler, whichever you officially have).

                   

                   

                  Grant