6 Replies Latest reply: May 13, 2016 6:20 PM by Stefan Wühl RSS

    Grouping List Box values

    Robin Neven

      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

        • Re: Grouping List Box values
          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

          • Re: Grouping List Box values
            Settu Periyasamy

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

            • Re: Grouping List Box values
              Marco Wedel

              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
                Marco Wedel

                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

                • Re: Grouping List Box values
                  Stefan Wühl

                  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'

                  )