Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Grouping List Box values

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

Capture.PNG

6 Replies

Re: Grouping List Box values

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

Capture.PNG

Re: Grouping List Box values

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

Not applicable

Re: Grouping List Box values

Solved! Thank you

Re: Grouping List Box values

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

Re: Grouping List Box values

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

MVP
MVP

Re: Grouping List Box values

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'

)

Community Browser