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....
