Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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],
Try with
sum(WildMatch(Order,'*CR*',1,0)) as [_cr],
hi
the syntax should be
sum(WildMatch(Order,'*CR*',1,0)) as [_cr],
Please try
sum(if(WildMatch(Codes,'*CR??*') and (not WildMatch(Codes,'*6YZZ*')),1,0)) as [_cr]
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.
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],
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;
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?
nope, I thought it was the values that would be returned on true/false. thanks for that!
That works perfectly, thanks Jonathan.