Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

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?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

10 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Try with

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

lironbaram
Partner - Master III
Partner - Master III

hi

the syntax should be

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

sasiparupudi1
Master III
Master III

Please try

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sasiparupudi1
Master III
Master III

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
Partner - Champion III
Partner - Champion III

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Not applicable
Author

That works perfectly, thanks Jonathan.