5 Replies Latest reply: Jun 16, 2016 9:23 AM by Stefan Wühl RSS

    ListBox with custom values in Qlik Sense

    Ram S

      I have following columns.  'TravelSummary', 'Date','Hotel Rate', 'Food Expenses' ,'Transport Expenses', 'Company Visited', ...etc etc

       

      Travel summary is a text field. The column TravelSummary will be similar to the below

       

       

       

      TravelSummary

      During this trip I visited the following offices in  London,Bristol, Rome, New York and Boston . Travel arrangement was made by Ms. Nancy. 

       


      I want to create a List Box with the following values. note that these values are not in table. It may be part of TravelSummary column

      City

      London

      Bristol

      Rome

      New York

      Boston

      I want to display chart  based on the List box 'City' selection. and search in TrabelSummary and generate chart.

      My Chart will have Dimension (Date(monthstart(Date), 'MMM-YY'))

      Measure will be Sum('Food Expenses') specific to the List Box selection if user selects.


      How do I implement as there is no List Box in Qlik Sense.


      Thank you




        • Re: ListBox with custom values in Qlik Sense
          Stefan Wühl

          I would suggest that you create a link table in your script that maps city names to your travel summary records.

           

          Similar to what is shown here:

          Multi-wildmatch on Text

           

          You would need to somehow create a list of possible city / offices names, maybe taken from a different table / data source (e.g. office locations).

            • Re: ListBox with custom values in Qlik Sense
              Ram S

              Thanks swuehl

               

              This solution gives me some inputs. https://community.qlik.com/thread/178148  But I am still figuring out how to port.

               

              About 4 years back I used to work on qlikview. If I remember correctly the version was 10.0 or 9.0

              We can simply add a listbox to the sheet, then add String manually.

               

              Then in my chart I can add dimension   (Date(monthstart(Date), 'MMM-YY'))

               

              measure. (based on the inputs from help from the community feedback )  (count(if(wildmatch(TravelSummary,'*word1*')>0,TravelSummary))). here I need to modify the "*word1*" with values from listbox.

               

              I am not sure how to implement this in Qlik Sense.

               

              Thanks.

                • Re: ListBox with custom values in Qlik Sense
                  Stefan Wühl

                  If you need more help, then please provide a small sample input file.

                   

                  Given the TravelSummary above and the Wikipedia table of some capital cities, I created a small sample QVF file to demonstrate how you can map a list of cities to your input records (similar to what is shown in the other thread).

                   

                  Note that the list of cities I've loaded does not contain all cities listed in your summary, so you might want to create another table (as suggested, maybe a table of cities where your offices are located).

                   

                  Hope this helps,

                  Stefan

                    • Re: ListBox with custom values in Qlik Sense
                      Ram S

                      Thanks swuehi. I am using the free version of qlikview as I am unable to install Qlik Sense on my computer.Can I directly use the qvf with free version of qlikview?

                        • Re: ListBox with custom values in Qlik Sense
                          Stefan Wühl

                          It's basically all done in the script part:

                           

                          INPUT:

                          LOAD *, Recno() as RecID INLINE [

                          TravelSummary

                          "During this trip I visited the following offices in  London,Bristol, Rome, New York and Boston . Travel arrangement was made by Ms. Nancy. "

                          "Then I travelled to Berlin and Hamburg"

                          "and then to Los Angeles and Luanda."

                          ];

                           

                          [@2]:

                          LOAD [Country or territory],

                               Capital,

                               [City proper],

                               [City proper1],

                               Source

                          FROM

                          [https://en.wikipedia.org/wiki/List_of_countries_by_national_capital,_largest_and_second-largest_cities]

                          (html, codepage is 1252, embedded labels, table is @2)

                          WHERE recno()>3;

                           

                           

                          TMP:

                          LOAD City, Len(City) as CityLen

                          RESIDENT [@2];

                           

                          MAP:

                          MAPPING LOAD City, '/'&City&'\' as F2

                          Resident TMP

                          order by CityLen desc;

                           

                           

                          DROP TABLE TMP;

                           

                           

                          LOAD RecID,

                             If(MappedCount,TextBetween(Mapped,'/','\',iterno()),'No City Found') as City

                          WHILE MappedCount >= iterno() or iterno()=1;

                          LOAD *,

                          SubstringCount(Mapsubstring('MAP', TravelSummary),'/') as MappedCount,

                          Mapsubstring('MAP', TravelSummary) as Mapped

                          RESIDENT INPUT;