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

WildMatch Not Showing Correct Count (grouping dimension)

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')


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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)
Group1Group11
Group1,Group 2,Group3,Group 4,Share1,Share 2,Share31
Total 2
Group2Group 2,Group3,Group 41
Group1,Group 2,Group3,Group 4,Share1,Share 2,Share31
Total 2
OtherGroup 2,Group3,Group 41
Group1,Group 2,Group3,Group 4,Share1,Share 2,Share31
Total 2
Total 3

View solution in original post

16 Replies
Chanty4u
MVP
MVP

try this and tell me what you are getting?

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

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

swuehl
MVP
MVP

Use something like

LOAD Group,

          GroupItem,

          If(WildMatch(GroupItem,'*Group1*','*Group2*')+1,'Other','Group1','Group2') as NewGroup;

LOAD

     Group,

     Subfield(Group, ',') as GroupItem

RESIDENT YourTable;

Then use NewGroup as dimension and Count(DISTINCT Group) as expression.

Chanty4u
MVP
MVP

try this

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

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

swuehl
MVP
MVP

To make your groups show the correct count, you need to create a link table, with a record per single group (that's what I am doing with the Subfield() function, creating a record per comma separated value in the original input record).

Take care that your matching values match the original string parts, e.g. Group1 vs Group 1 etc.

Not applicable
Author

I'm still getting same count in bar chart,

Group 1 shows 68 and Group 2 shows 2 when it should show 13

Not applicable
Author

Hey Chanty, that changed the count but it made it incorrect, it bumped the Group1 count to the Group2 count.

Not applicable
Author

Hi Stefan, I'm looking up how to create a link table in Qlik Sense

Not applicable
Author

I can't get this to work:

LOAD

     GroupField

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

(ooxml, embedded labels, table is PublicConnection);

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

LOAD

     Group,

     SubField(Group,',') as GroupField

Resident YourTable;

swuehl
MVP
MVP

Maybe like this (note the change to Pick() function below):

INPUT: // I assume you are loading also other fields / facts?

LOAD

     GroupField

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

(ooxml, embedded labels, table is PublicConnection);

LINK:

LOAD GroupField,

          GroupItem,

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

LOAD

     GroupField,

     Subfield(GroupField, ',') as GroupItem

RESIDENT INPUT;