Grouping List Box values

[#1]

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!

[sunny_talwar · Accepted solution]

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

[settu_periasamy]

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

[#4]

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:

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

[MarcoWedel]

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

[swuehl]

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'

)