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


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


      the script I am trying is here:




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

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

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




      (ooxml, embedded labels, table is data)


      Group by



      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?