10 Replies Latest reply: Jul 14, 2015 4:07 PM by Marco Wedel RSS

    How to group by a partial string value in a field

    Fraser Anderson

      I am have a field which can contain a number of codes.  The number of codes contained in the field changes and the contents also change.  The codes are always three or four characters.

       

      I want to be able to search within this field for the number of occurrances of a particular code and set a counter based on it.  I have been able to do this with unique 4 digit codes, but not when I want to find everything with a specific prefix, "CR" for example

       

      OrderCodes
      1234RGLI
      1234RGLI CR23
      1234CR23 6YZZ
      5678RGLI 6YZZ
      5678RGLI 6YZZ

       

      the script I am trying is here:

       

      LOAD

        Order,

        sum(WildMatch(Order,'*RGLI*',1,0)) as [_rgli],

        sum(WildMatch(Order,'*6YZZ*',1,0)) as [_6yzz],

        sum(WildMatch(Order,'*CR??*',1,0)) as [_cr],

       

      FROM

      [data.xlsm]

      (ooxml, embedded labels, table is data)

       

      Group by

        Order

       

      the output I want to have is:

      1234 with _rgli value 2 _6yzz value 1 and _cr value 2

      5678 with _rgli value 2 _6yzz value 2 and _cr value 0

       

      the only thing that does not work is the _cr value, I am not sure what the right syntax should be....

       

      any ideas?