Multi Line Field Help
Altair EmployeeLet 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
Product
Consumer Relief
Loan Number Loan Type Denial Reasons Member Number Amount Approved Approval Officer Decision Comments
1234567 PL APPROVED 111111111111
FRAUD WARNING: CREDIT REPORT ALERT SECTION HAS FRAUD KEYWORDS:
* 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 Number | Date | Product | Failed Reason |
|---|---|---|---|
| 1234567 | 9/1/2017 | Consumer Relief | FRAUD WARNING: CREDIT REPORT ALERT SECTION HAS FRAUD KEYWORDS: |
| 1234567 | 9/1/2017 | Consumer Relief | * ID_MISMATCH |
| 1234567 | 9/1/2017 | Consumer Relief | Applicant credit score (621) is below the minimum (720) |
| 1234567 | 9/1/2017 | Consumer Relief | Customer chargeoff period (13) is within: 36 months |
Best Answer
-
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.
0
Answers
-
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.
0 -
Altair Forum User said:
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.
It worked and I am ecstatic! So simple and I was so close just a rookie overlook. Thank you so much!
For the record I'm on version 13 and this is a text file report. THANK you again!
0 -
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:
- Continue using the Loan Number line as the Detail and capture the Failed Reasons as a mult-line field.
- Open the Model and report in Data Prep Studio.
- Split the Failed Reason column into parts using the \n newline as the delimiter.
- Unpivot the table using the multiple Failed Reason columns as keys.
- This will produce the following table, and you need only delete the Attribute column and rename the Value column.
Again, this would only be necessary if for some reason you were unable to trap successfully the lines with the Failed Reasons. !
0
