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

    How to summarize a field with various length records

    F77 _

      I am trying to figure out how to summarize a field that has various length records 2, 3 or 5 letter combinations. They currently are all seperated by a comma however I am thinking it is easier to seperate them with a space in case I need to do this in excel.  Here is a rough example. Is there a function, or a calculation, or am I missing something simple in order to do this? I am using Monarch V8 Pro

       

      Customer       Location

      Customer1      AL,AK,CA

      Customer2      ALL,AK,WI

      Customer3      Div,CA,MN

       

      What I want my summary to show is by location

      AL  1

      ALL 1

      AK  2

      CA  2

      DIV 1

      MN  1

      WI  1

       

      Thanks in Advance.

        • How to summarize a field with various length records
          Grant Perkins

          Hi F77,

           

          An interesting challenge. To get the summary analyis you need the field values (AK, AL, ALL, etc.) need to be values of a single field. As it is you can seperate the values into different calculated fields but not easily into a single field - at least not in one pass.

           

          However if the variable width 'columns' could be re-spaced and a new version of the report produced it looks from the sample you have posted to be a perfect candidate for the V8 Multi Column Region processing feature.

           

          So how to re-format?

           

          That sort of depends on whether the commas are used elsewhere in the report.

           

          If not you could use the MSRP.EXE utility to replace the commas with either a number of spaces or perhaps a TAB in order to replace the columns and produce a revised file. From the revised file it should be possible to create a model that uses MCR to pick each of the values into the the same fieldname, complete the the Customer number info,  and the generate the summary you require.

           

          (I know of some other tools that offer similar facilities to MSRP and offer a higher degree of interactive control if that would be useful. However they are not designed for automated use so which tool is best will be influenced by production factors. Send me a Private Message if you want more details.)

           

           

          If you wanted to avoid the MSRP utility or if the use of commas in the report means that swapping comma for TAB or whatever would cause other problems, you could have a 2 stage Monarch process.

           

          Stage one would re-format the file in a similar way by capturing the comma separted values as a single field and then using the LSPLIT function (or whatever function you prefer) to create calculated fields or each value in the field.

           

          Export the Customer field and the new calculated fields to a new fixed field width report. Then use another Monarch model on the new report using MCR functionality.

           

          Yet another approach (untried so my create some issues) would be to create a 2 column output for Customer and then 'the rest'. Export this as a CSV file WITHOUT quoted text. With luck that would add a comma between the Customer and the existing comma separated values.

           

          If that worked OK you could then read the resulting file into Monarch as a database.

           

          I'm sure there will be other ideas as well but these should be fairly quick suggestions to try out.

           

          Let us know how you get on.

           

          Grant