8 Replies Latest reply: May 15, 2014 10:05 AM by Data Kruncher RSS

    new user - multiple lines

    karenTC _

      I am trying to capture information that, for the most part is on one line.  However, some records have 2 lines.  The problem is the consistency of the second line.  The first field of the first line always has an account number.  However the second line, the only consistency is the account field is blank.  Other than that, the information may be in any of the other fields, but other wise is blank.

       

      The columns all line up exactly.  I've tried as separate lines with the second being the detail and the first being the append, but it only captures records with 2 lines.  If I reverse the append and detail, the second line is attached to the next record.

       

          173737 Smith, MAXIN         105.23  12.30     .00     .00     .00       85.28

                                                                      7.38

          173741 Smith, MARY           77.83  21.73     .00     .00     .00       53.07

          173742 Smith, JOHN            81.14  21.78     .00     .00     .00       57.13

          173743 Harold, ED               17.07  20.05 17.40    .00     .00       13.42

          173745 Barnes, GAIL           17.07  20.05  17.40     .00     .00       13.42

          173743 Gamble, Bill           130.43  21.30     .00     .00     .00      137.13

          173747 Bramble, FLORENCE  21.23    .00      .00     .00     .00       21.23

          173748 Markham, GRADY     21.82  17.58     .00     .00     .00        2.24

          173747 Belfry, IRENE             .00  18.85  128.88     .00     .00      110.03

                 DISCOUNTS:                .00     7.30     28.87                        17.27

        • new user - multiple lines
          Data Kruncher

          Hi Karen. Welcome to the forum.

           

          Is the 7.38 value for the 173737 record actually in its own line in the data file, or is that just a formatting issue in this post?

           

          I guess I'm really asking if the DISCOUNTS item is the only 2nd line item that you want to tack on to the 173747 Irene Belfrey record, or if the 7.38 is one too. We can probably handle either case; I'd just like to clarify before we get going.

            • new user - multiple lines
              karenTC _

              Thanks for the quick response.  The 7.38 is actually farther over on the second line.  The second line ALWAYS begins with blanks where normally there would be an account number.  After that, it MAY hold the word "DISCOUNTS" or it might just be a lone number or two numbers such as the 7:38.  If every second line contained the word "DISCOUNTS", it would be easier.  But really, the only common denominator is the blanks where there would be an account number if it were a new record.  The second line contains additional information relevant to the account, when it pertains, which isn't frequently.  What I can say is that all number line up exactly. 

               

              What I'd like to be able to do is begin a new record when there is a value (non-blank) in the first column.

               

              Monarch is fabulous, .... when I can get it to work.  ANd I know there is a lot for me to understand yet.

                • new user - multiple lines
                  Data Kruncher

                  OK, in order to extract this properly, you'll need to employ two append templates. Specifically, you'll need two [URL="http://********************/extract/experimentation-produces-monarch-success"]"guru trap"[/URL] templates. I'd recommend that you spend a couple of minutes reading the linked item before preceding below. It'll help to make sense of this.

                   

                  Keeping things simple with your example, I built a detail template which had a single N in column 1, using a one line sample. Paint the field(s) as you would normally.

                   

                  Now select the Discounts line as a sample, and for the trap, again use a single N in column 1 for the trap and paint the discount values as necessary. For each of these fields, on the Advanced tab specify that the word discount appears on the same line. Save the template.

                   

                  Repeat this process for the 7.38 line, except that this time you'll specify that 4 spaces appear in the same line using the Advanced field properties.

                   

                  HTH,

                  Kruncher