topic Re: Grouping List Box values in QlikView

Grouping List Box values

Fri, 13 May 2016 14:46:02 GMT

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!

Re: Grouping List Box values

sunny_talwar — Fri, 13 May 2016 16:13:27 GMT

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

Re: Grouping List Box values

settu_periasamy — Fri, 13 May 2016 16:57:15 GMT

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

Re: Grouping List Box values

Fri, 13 May 2016 18:29:57 GMT

Solved! Thank you

Re: Grouping List Box values

MarcoWedel — Fri, 13 May 2016 20:04:36 GMT

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:

and in a chart:

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

MarcoWedel — Fri, 13 May 2016 21:40:42 GMT

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

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

Re: Grouping List Box values

swuehl — Fri, 13 May 2016 22:20:22 GMT

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'

)