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

# How to group by a partial string value in a field

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:

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?

• ###### Re: How to group by a partial string value in a field

Try with

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

• ###### Re: How to group by a partial string value in a field

hi

the syntax should be

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

• ###### Re: How to group by a partial string value in a field

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.

• ###### Re: How to group by a partial string value in a field

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

• ###### Re: How to group by a partial string value in a field

or You could try

T1:

Order,Codes

1234,RGLI

1234,RGLI CR23

1234,CR23 6YZZ

5678,RGLI 6YZZ

5678,RGLI 6YZZ

];

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;

• ###### Re: How to group by a partial string value in a field

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],

• ###### Re: How to group by a partial string value in a field

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?

• ###### Re: How to group by a partial string value in a field

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

• ###### Re: How to group by a partial string value in a field

That works perfectly, thanks Jonathan.

• ###### Re: How to group by a partial string value in a field

maybe also possible:

-Sum(Codes like 'CR*')

hope this helps

regards

Marco