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.

[Chanty4u]

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]

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]

try this

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

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

[swuehl]

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.

[#6]

I'm still getting same count in bar chart,

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

[#7]

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

[#8]

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

[#9]

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]

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;