ListBox with custom values in Qlik Sense

[Anonymous]

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

[swuehl]

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).

[Anonymous]

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.

[swuehl]

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

[Anonymous]

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?

[swuehl]

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_citi...]

(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;