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

Grouping List Box values

Hello there,

Trying to group values in a list box. Got pretty far with some other discussions here, but not far enough. Here's the expression I use for the list box:

=IF(WildMatch(DefectsList.Software_Version,'And*'),'Android',

IF(WildMatch(DefectsList.Software_Version,'AVS*'),'AVS',

IF(WildMatch(DefectsList.Software_Version,'iOS*'),'iOS',

IF(WildMatch(DefectsList.Software_Version,'iTV*','ITV*'),'iTVOnline',

IF(WildMatch(DefectsList.Software_Version,'PCTV*'),'PCTV',

IF(WildMatch(DefectsList.Software_Version,'Win8*'),'Win8',

IF(not WildMatch(DefectsList.Software_Version,'And*','AVS*','iOS*','iTV*','ITV*','PCTV*','Win8*'),'Other'

)))))))

This gives me the following, but instead I want it grouped on the right column, so it only shows Android, iOS, iTVOnline etc. once so you can choose from either one of them. Thank you in advance!

List Box to group.PNG

1 Solution

Accepted Solutions
sunny_talwar

I think you are not using the expression in the right spot. You need to use this expression on the general tab.

Capture.PNG

View solution in original post

6 Replies
sunny_talwar

I think you are not using the expression in the right spot. You need to use this expression on the general tab.

Capture.PNG

settu_periasamy
Master III
Master III

Remove your Expression from the "Expression Tab" , put it in the General Table 'Expression' which is showed by stalwar1

Not applicable
Author

Solved! Thank you

MarcoWedel

Hi,

if you always want the first part of a string up to the first space then some precalculated field using the SubField() function might be a solution. Maybe like this:

Using a drill down group in a ListBox:

QlikCommunity_Thread_216852_Pic1.JPG

QlikCommunity_Thread_216852_Pic3.JPG

QlikCommunity_Thread_216852_Pic2.JPG

and in a chart:

QlikCommunity_Thread_216852_Pic4.JPG

QlikCommunity_Thread_216852_Pic5.JPG

table1:

LOAD *,

    SubField(DefectsList.Software_Version,' ',1) as DefectsList.Software,

    Ceil(100*Rand()) as fact

INLINE [

    DefectsList.Software_Version

    Android 1.1

    Android 1.2

    Android 1.3

    Android 1.4

    Android 1.5

    AVS 2.1

    AVS 2.2

    AVS 2.3

    AVS 2.4

    AVS 2.5

    IOS 3.1

    IOS 3.2

    IOS 3.3

    IOS 3.4

    IOS 3.5

    ITVOnline 4.1

    ITVOnline 4.2

    ITVOnline 4.3

    ITVOnline 4.4

    ITVOnline 4.5

    PCTV 5.1

    PCTV 5.2

    PCTV 5.3

    PCTV 5.4

    PCTV 5.5

    Win8 6.1

    Win8 6.2

    Win8 6.3

    Win8 6.4

    Win8 6.5

];

hope this helps

regards

Marco

MarcoWedel

If you can't ensure a space after your initial substring, then another solution might be:

QlikCommunity_Thread_216852_Pic6.JPG

mapDefectList:

Mapping

LOAD *,

     '@start@'&Software&'@end@'

INLINE [

    Software

    Android

    AVS

    IOS

    ITVOnline

    PCTV

    Win8

];

table1:

LOAD *,

     If(MapSubString('mapDefectList',DefectsList.Software_Version) like '@start@*',TextBetween(MapSubString('mapDefectList',DefectsList.Software_Version),'@start@','@end@')) as DefectsList.Software,

     Ceil(100*Rand()) as fact

INLINE [

    DefectsList.Software_Version

    Android 1.1

    Android1.2

    someAndroid1.3

    Android1.4

    anotherAndroid 1.5

    AVS 2.1

    anyAVS2.2

    AVS 2.3

    AVS 2.4

    AVS2.5

    IOS 3.1

    my IOS3.2

    IOS 3.3

    IOS 3.4

    IOS3.5

    ITVOnline 4.1

    your ITVOnline4.2

    ITVOnline 4.3

    ITVOnline4.4

    ITVOnline 4.5

    PCTV5.1

    another PCTV 5.2

    PCTV5.3

    PCTV 5.4

    PCTV 5.5

    Win86.1

    Win8 6.2

    something Win8 6.3

    Win86.4

    Win8 6.5

];

hope this helps

regards

Marco

swuehl
MVP
MVP

Just as a side note to your original expression:

=IF(WildMatch(DefectsList.Software_Version,'And*'),'Android',

IF(WildMatch(DefectsList.Software_Version,'AVS*'),'AVS',

IF(WildMatch(DefectsList.Software_Version,'iOS*'),'iOS',

IF(WildMatch(DefectsList.Software_Version,'iTV*','ITV*'),'iTVOnline',

IF(WildMatch(DefectsList.Software_Version,'PCTV*'),'PCTV',

IF(WildMatch(DefectsList.Software_Version,'Win8*'),'Win8',

IF(not WildMatch(DefectsList.Software_Version,'And*','AVS*','iOS*','iTV*','ITV*','PCTV*','Win8*'),'Other'

)))))))

You don't need to do the last if() conditional, since DefectsList.Software_Version value can't be any of the listed values if has not fulfilled any of the previous if() conditions;


=IF(WildMatch(DefectsList.Software_Version,'And*'),'Android',

IF(WildMatch(DefectsList.Software_Version,'AVS*'),'AVS',

IF(WildMatch(DefectsList.Software_Version,'iOS*'),'iOS',

IF(WildMatch(DefectsList.Software_Version,'iTV*','ITV*'),'iTVOnline',

IF(WildMatch(DefectsList.Software_Version,'PCTV*'),'PCTV',

IF(WildMatch(DefectsList.Software_Version,'Win8*'),'Win8',

'Other'

))))))

Then, you can use a single Wildmatch() with several values to test and an additional pick() function:

=Pick(

WildMatch(DefectsList.Software_Version,'And*','AVS*','iOS*','iTV*','PCTV*','Win8*')+1, //add 1 (start with no match found)

.'Other','Android','AVS','iOS','iTVOnline','PCTV','Win8'

)