How to group by a partial string value in a field

[#1]

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

Order	Codes
1234	RGLI
1234	RGLI CR23
1234	CR23 6YZZ
5678	RGLI 6YZZ
5678	RGLI 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?

[alexandros17]

Try with

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

[lironbaram]

hi

the syntax should be

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

[sasiparupudi1]

Please try

sum(if(WildMatch(Codes,'*CR??*') and (not WildMatch(Codes,'*6YZZ*')),1,0)) as [_cr]

[#5]

Hi, that was what I tried first.  I should have mentioned that the CR has to be in the first position of the code.  the '*CR*' picks up all the CRs, including NOCR for example, which is not correct.

[jonathandienst · Accepted solution]

Not sure why anything works. What you need is something like this:

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

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

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

[sasiparupudi1]

or You could try

T1:

Load * INLINE [

Order,Codes

1234,RGLI

1234,RGLI CR23

1234,CR23 6YZZ

5678,RGLI 6YZZ

5678,RGLI 6YZZ

];

LOAD

  Order,

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

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

  //sum(if(WildMatch(Codes,'*CR??*') and (not WildMatch(Codes,'*6YZZ*')),1,0)) as [_cr]

  sum(WildMatch(SubField(Codes,' ',1),'CR??',1,0)) as [_cr]

Resident T1

Group by Order;

drop Table T1;

[jonathandienst]

Syntax of WildMatch:

     WildMatch('string to search', 'search pattern1', 'search pattern2', ....'search pattern n')

You have search pattern 2 = 1 and search pattern 3 = 0. Is that really what you want?

[#9]

nope, I thought it was the values that would be returned on true/false.  thanks for that!

[#10]

That works perfectly, thanks Jonathan.