WildMatch Not Showing Correct Count (grouping dimension)

[#1]

I have a text field/column that has multiple values separated by a comma. I have added it in a chart as my dimension. Each row for that field contains data, for example, "Group1,Group 2,Group3,Share 1,Share 2". Each row/column can multiple values (as in the example) or only one value (Group1). When I apply the formula,

       if(WildMatch([Group],'*Group1*','*Group 2*')>0,

       pick(WildMatch([Group],'*Group1*','*Group 2*'),'Group 1','Group 2'),'Other')

Group	Header 2
Group1,Group 2,Group3,Group 4,Share1,Share 2,Share3
Group1
Group 2,Group3,Group 4

I'm expecting all of the data to be grouped accordingly in the bar chart according to its group I've specified with accurate count. But it seems that only the first value, Group1, has the correct count. All of the other values do not have the correct count.

[swuehl · Accepted solution]

Here is a working QlikView script using above sample data:

INPUT:

LOAD Group,

     [Header 2]

FROM

[https://community.qlik.com/thread/275122]

(html, codepage is 1252, embedded labels, table is @1);

LINK:

LOAD Group,

          GroupItem,

          Pick(WildMatch(GroupItem,'*Group*1*','*Group*2*')+1,'Other','Group1','Group2') as NewGroup;

LOAD

     Group,

     Subfield(Group, ',') as GroupItem

RESIDENT INPUT;

NewGroup	Group	Count(DISTINCT Group)
Group1	Group1	1
	Group1,Group 2,Group3,Group 4,Share1,Share 2,Share3	1
	Total	2
Group2	Group 2,Group3,Group 4	1
	Group1,Group 2,Group3,Group 4,Share1,Share 2,Share3	1
	Total	2
Other	Group 2,Group3,Group 4	1
	Group1,Group 2,Group3,Group 4,Share1,Share 2,Share3	1
	Total	2
Total		3

[#3]

Stefan, much better, it's now correct in terms of the count. But the alphabetic sorting is off, possibly because of spaces in the data. Anyway to remedy that?

[#4]

Also, if I have a bunch of values, do I have to enter all of them in the function:

     Pick(WildMatch(GroupItem,'*Group*1*','*Group*2*')+1,'Other','Group1','Group2') as

[swuehl]

Joe Ski wrote:

Stefan, much better, it's now correct in terms of the count. But the alphabetic sorting is off, possibly because of spaces in the data. Anyway to remedy that?

Not sure if I understand, can you give an example?

Since you are defining the new group values yourself, you should be able to control the spaces.

In addition, you can use Dual() function to define a sort order as you like

Data Types in QlikView

[swuehl]

Joe Ski wrote:

Also, if I have a bunch of values, do I have to enter all of them in the function:

     Pick(WildMatch(GroupItem,'*Group*1*','*Group*2*')+1,'Other','Group1','Group2') as

You can also do it the other way round, if that's easier, defining the Other group:

If( Wildmatch(GroupItem,'*Share*','*Group*4*'), 'Other', GroupItem) as NewGroup

[#7]

Here's what ended up working for me:

LOAD

     GroupItem,ID

FROM [lib://AttachedField/GroupField.xlsx]

(ooxml, embedded labels, table is PublicConnection);

LINK:

Load

     GroupItem,

     GroupItemNew,

     Pick(WildMatch(GroupItem,'*Group*1*','*Group*2*')+1,'Other','Group1','Group2') as NewGroupItem;

Load

     GroupItem,

     SubField(GroupItem,',') as GroupItemNew;

[#8]

the value labels appear as follows in the bar chart:

Actions

Commercial

GC

CIO

Armtight

IARI

but I'm not able to sort them alphabetically. above is what I get