🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

Trapping Multiple Rows

User: "Tom S"
Altair Community Member
Updated by Tom S

How can I trap multiple rows into one cell?  I am trying to extract transactions from a bank statement.  Each transaction will have one line with a date, some description, deposits, withdrawals and balance and some transactions may have 1 or 2 additional rows with additional description(s).  I would like to place the total description in one cell.

 

Find more posts tagged with

Sort by:
1 - 5 of 51
    User: "CPorthouse"
    Altair Employee
    Updated by CPorthouse

    It looks like you can probably use a two line sample for your detail. Draw your description field on the second line and then use the advanced property to end the field on a blank field value.  It will automatically terminate at the start of the next detail record.

    I will use one of our sample files for a demonstration.

    In this case I have an employee record that contains an address as well as an Employee #, Office, and Department.  I would select all three lines as my sample data:

    image

    Select a new template (in my case "Detail"), and build the trap on the first line.  You do not have to use the first line, but it is the best choice in this case.

    image

    You can then select the fields you want to use.  In this example, I would only select the first line for address and then use the advanced properties (or edit field if using classic) to change how I end the field to use "Blank field values: 1":

     

    image

    The result:

    image

    You can also define the other three fields, if needed.  The final result:

    image

     

    User: "Baba_Majekodunmi_703"
    Altair Employee
    Updated by Baba_Majekodunmi_703

    Hi Tom,

    Nice to meet you. The sample document you attached is a scanned document, which doesn't render in Monarch. You'll need to get a copy of the original digital document for your needs.

    You should be able to use one of these 'End Field On' features in the Advance settings that Chris just described. Here are some helpful links and videos below to help you.

    Additional References:

    Monarch Success Plan

    Monarch Exercise 2 Video Answer

    Monarch Exercise 4 Video Answer

    Classic View (Chris is showing the Data Prep Studio view of the same settings below)

    image

    User: "Tom S"
    Altair Community Member
    OP
    Updated by Tom S

    I am attaching a pdf version of the document I am wanting to extract data from.  Please note on page two, there is a transaction with three lines.  How do I capture the description on lines 1, 2 and 3 and either put in one cell or multiple cells and still capture the transactions with only 1 or 2 rows.

    Also, please note the transactions on the 1st page are indented differently that the transactions on page 2.

    Thanks for any assistance you can provide.

    User: "CPorthouse"
    Altair Employee
    Updated by CPorthouse

    I am attaching a pdf version of the document I am wanting to extract data from.  Please note on page two, there is a transaction with three lines.  How do I capture the description on lines 1, 2 and 3 and either put in one cell or multiple cells and still capture the transactions with only 1 or 2 rows.

    Also, please note the transactions on the 1st page are indented differently that the transactions on page 2.

    Thanks for any assistance you can provide.

    Thank you for that sample.  You did correctly identify that the descriptions are indented differently between page 1 and 2.  One additional suggestion you might want to consider is to adjust the PDF engine being used, if for no other reason, the 4.5 engine is not needed and can have a factor on performance.  I changed to the 3.0 or 4.0 engine and everything still lined up as expected (with the funny indenting where the description starts).  Having said that, the original solution is still the correct one in this situation.

    I was able to trap on the date ÑÑ-ÑÑ to grab the first line of each record.  I then drew the field for description and under the advanced tab, select Blank field values: 1

    image

     

    The data on the second page will have leading spaces, but you can always clean that up on the prepare tab or in Monarch Classic.

    image

    Attaching a sample workspace and model.

    User: "Rebecca_Cronin"
    Altair Employee
    Updated by Rebecca_Cronin

    Hi @Tom S ,  

     

    Testing your sample data set and in trapping the Description Field while utilizing the Advanced Properties for the "END FIELD ON" - the 2nd option seemed to capture your information.  See screen shot image of the selection you may need in order to capture what you need. 

    I also wanted to share with you link to the Monarch Help Guide that describes more about Multiple Line Fields

     

    image