topic Re: How to group by a partial string value in a field in QlikView

How to group by a partial string value in a field

Tue, 14 Jul 2015 13:22:14 GMT

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?

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

alexandros17 — Tue, 14 Jul 2015 13:25:14 GMT

Try with

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

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

lironbaram — Tue, 14 Jul 2015 13:31:59 GMT

the syntax should be

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

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

sasiparupudi1 — Tue, 14 Jul 2015 13:55:11 GMT

Please try

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

Tue, 14 Jul 2015 14:02:35 GMT

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

jonathandienst — Tue, 14 Jul 2015 14:04:36 GMT

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

sasiparupudi1 — Tue, 14 Jul 2015 14:08:44 GMT

or You could try

T1:

Load * INLINE [

Order,Codes

1234,RGLI

1234,RGLI CR23

1234,CR23 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;

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

jonathandienst — Tue, 14 Jul 2015 14:53:08 GMT

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

Tue, 14 Jul 2015 14:55:02 GMT

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

Tue, 14 Jul 2015 15:20:26 GMT

That works perfectly, thanks Jonathan.

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

MarcoWedel — Tue, 14 Jul 2015 20:07:27 GMT

maybe also possible:

-Sum(Codes like 'CR*')

hope this helps

regards

Marco