3 Replies Latest reply: Mar 28, 2018 8:30 AM by Stephen Smay RSS

    Multi Line Field Help

    Raymond Glass

      Let me preface this discussion by saying I am pretty new to the software.  I have reached full exhaustion of ideas when it comes to trying to create the model I am looking for on this report.  The detail field (Failed Reason) encompasses multiple lines but I need each line to be a different row in the table.  In the example below, there would be multiple rows (4, not counting blank rows) for loan number 1234567 and there would be 6 for loan number 1234568.  I have set up the traps seemingly properly to where they are extracting the correct data, but of course all of the lines for detail are combined and regurgitated into a single cell, which does me no good.  From what I can also tell, the TextLine function will not help me either since I'd have to make upwards of 30 formula fields to try and cover the amount of lines there could possibly be in the raw data file.


      Any help I can get would be much appreciated.  Thank you.


      From 9/1/2017 - 9/30/2017                


      Consumer Relief               

      Loan Number    Loan Type Denial Reasons  Member Number    Amount Approved   Approval Officer  Decision Comments

      1234567    PL APPROVED  111111111111 




           * ID_MISMATCH           

           Applicant credit score (621) is below the minimum (720)           

           Customer chargeoff period (13) is within: 36 months           

      1234568    PL APPROVED  111111111111 



           Applicant credit score (530) is below the minimum (720)           

           Customer Install Late From Now 120 (34) is over the maximum: 0 instance(s)           

           Customer Install Late From Now 120 Within 1 Yr  (10) is over the maximum: 0 instance(s)           

           Customer collection period (0) is within: 24 months           

           Customer number of collections (4) is greater than 3           

           "Customer collections amount ($3,348.00) is greater than $300.00"


      Example Desired Data Output:

      Loan NumberDateProductFailed Reason
      12345679/1/2017Consumer Relief  * ID_MISMATCH      
      12345679/1/2017Consumer Relief  Applicant credit score (621) is below the minimum (720)  
      12345679/1/2017Consumer Relief 

      Customer chargeoff period (13) is within: 36 months 

        • Re: Multi Line Field Help
          Chris Porthouse

          What version of Monarch are you using and what is the file type (PDF, excel, text) where the data is coming from?  I copied and pasted your example into notepad and was able to model this up:

          I set the failed reason to be a detail template and used a pattern that would only capture those lines. Blank in the first column, blank before the failed reason followed by a non-blank.  Did you make your failed reason field to be a multi-line field? I would leave the field type as Character and not memo (if you chose that option).  Also, if you look at the advanced option for that field, what do you have for "End Field On"?  That should be set to a line count of 1.


          You are on the right track to make the failed reason to be a detail template.  You can then make the loan number, date, and product appends.  You can have multiple appends but only one detail.

          • Re: Multi Line Field Help
            Stephen Smay

            Chris Porthouse cporthouse has indeed given the simplest and most direct solution, but I want to add that there is another way to go about this, for situations where anyone may be unable to trap the Detail line with the Failed Reason. (since it seems to be free text in there, the method of using ßØ to identify the beginning of the field will only work if the field begins consistently at that position on the line, and if there are no other lines that happen to feature that pattern at that position)


            Anyway, that method is:

            1. Continue using the Loan Number line as the Detail and capture the Failed Reasons as a mult-line field.
            2. Open the Model and report in Data Prep Studio.
            3. Split the Failed Reason column into parts using the \n newline as the delimiter.
            4. Unpivot the table using the multiple Failed Reason columns as keys.Unpivot multi-line field.png
            5. This will produce the following table, and you need only delete the Attribute column and rename the Value column.Unpivoted Table.png

            Again, this would only be necessary if for some reason you were unable to trap successfully the lines with the Failed Reasons. !