Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

View solution in original post

Not applicable

Re: WildMatch Not Showing Correct Count (grouping dimension)

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 applicable

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

MVP
MVP

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

MVP
MVP

Re: WildMatch Not Showing Correct Count (grouping dimension)

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

Highlighted
Not applicable

Re: WildMatch Not Showing Correct Count (grouping dimension)

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;

Not applicable

Re: WildMatch Not Showing Correct Count (grouping dimension)

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