Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
MVP
MVP

Re: WildMatch Not Showing Correct Count (grouping dimension)

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
16 Replies
sureshqv
Esteemed Contributor III

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

MVP
MVP

Re: WildMatch Not Showing Correct Count (grouping dimension)

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.

sureshqv
Esteemed Contributor III

Re: WildMatch Not Showing Correct Count (grouping dimension)

try this

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

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

MVP
MVP

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

Not applicable

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

Not applicable

Re: WildMatch Not Showing Correct Count (grouping dimension)

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;

MVP
MVP

Re: WildMatch Not Showing Correct Count (grouping dimension)

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;

Community Browser