1 Reply Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    sort-of newbie question

    PL _

      Hello. I've been using Monarch for a little while now but ran into an append template program that I couldn't resolve today.

       

      My data look like this:

                        FIELD1    FIELD2   FIELD3

      33333 CITY NAME

      CATEGORY 1          10        12       14

      CATEGORY 2          11        24       35 

      CATEGORY 3          16        22       12

      33333 CITY NAME

      CATEGORY 1          10        12       14

      CATEGORY 2          11        24       35 

      CATEGORY 3          16        22       12

      COUNTY NAME-COUNTY

      CATEGORY 1          10        12       14

      CATEGORY 2          11        24       35 

      CATEGORY 3          16        22       12

      33333 CITY NAME

      CATEGORY 1          10        12       14

      CATEGORY 2          11        24       35 

      CATEGORY 3          16        22       12

       

      And it repeats in a similar - though not exact - pattern.

      I can get it so it looks like this in a table:

      33333 CITY NAME  CATEGORY 1   10   12   14

      33333 CITY NAME  CATEGORY 2   11   24   35

      33333 CITY NAME  CATEGORY 3   16   22   12

      etc...

      COUNTY NAME-COUNTY CATEGORY 1   10   12  14

      COUNTY NAME-COUNTY CATEGORY 2   11   24  35

      COUNTY NAME-COUNTY CATEGORY 3   16   22  12

       

      But I WANT it to look like this:

      CITY NAME CATEGORY 1 10  12  14

      CITY NAME CATEGORY 2 11  24  35

      CITY NAME CATEGORY 3 16  22  12

      COUNTY  CATEGORY 1 10  12  14

      COUTNY  CATEGORY 2 11 24 35

       

      I can't seem to set a trap to do that without having one or the other repeat on top of the other one. I hope this makes sense.

       

      Thanks for any and all help and sorry for the long message.

        • sort-of newbie question
          Grant Perkins

          Originally posted by PL:

          My data look like this:

           

          [font="courier"]                  FIELD1    FIELD2   FIELD3

          33333 CITY NAME

          CATEGORY 1          10        12       14

          CATEGORY 2          11        24       35 

          CATEGORY 3          16        22       12

          33333 CITY NAME

          CATEGORY 1          10        12       14

          CATEGORY 2          11        24       35 

          CATEGORY 3          16        22       12

          COUNTY NAME-COUNTY

          CATEGORY 1          10        12       14

          CATEGORY 2          11        24       35 

          CATEGORY 3          16        22       12

          33333 CITY NAME

          CATEGORY 1          10        12       14

          CATEGORY 2          11        24       35 

          CATEGORY 3          16        22       12

           

          And it repeats in a similar - though not exact - pattern.

          I can get it so it looks like this in a table:

          33333 CITY NAME  CATEGORY 1   10   12   14

          33333 CITY NAME  CATEGORY 2   11   24   35

          33333 CITY NAME  CATEGORY 3   16   22   12

          etc...

          COUNTY NAME-COUNTY CATEGORY 1   10   12  14

          COUNTY NAME-COUNTY CATEGORY 2   11   24  35

          COUNTY NAME-COUNTY CATEGORY 3   16   22  12[/font][/quote]But I WANT it to look like this:

           

          [font="courier"]CITY NAME CATEGORY 1 10  12  14

          CITY NAME CATEGORY 2 11  24  35

          CITY NAME CATEGORY 3 16  22  12

          COUNTY  CATEGORY 1 10  12  14

          COUTNY  CATEGORY 2 11 24 35[/font][/quote]I can't seem to set a trap to do that without having one or the other repeat on top of the other one. I hope this makes sense.

          /b[/quote]I'm not quite sure what you mean by "having one or the other repeat on top of the other one." but I'm going to guess you have set up separate append templates for City and County.

           

          I think what you need to do is treat them as one extracted field, probably the entire string rather than trying to separate the fields in the template, and then manipulate the results using calculated fields to extract just the fields you want from the long string that will result. As part of that you should also be in a position to flag the record type as either City or County and then use the flag to get the sort order you desire.

           

          If the city lines start with a numeric and the county line don't you can use something like the ISALPHA() function to set the type flag.

           

          Use one of the SPLIT functions, LSPLIT seems appropriate, to cut the numeric code of the beginning of the CITY records. Probably the same split or RSPLIT() for the COUNTY information.

           

          Consider the use of the TRIM functions when using splits in case there are sometimes double spaces which produce unexpected results.

           

          If these suggestions are unclear or don't work for you let us know together woth examples of what you start with and what you get as a result and I will try to work them in more detail - I'm a bit short of time just at this moment.

           

          HTH.

           

          Grant