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:
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).
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.
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,
comm219403.qvf 192.0 K
It's basically all done in the script part:
LOAD *, Recno() as RecID INLINE [
"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."
LOAD [Country or territory],
(html, codepage is 1252, embedded labels, table is @2)
LOAD City, Len(City) as CityLen
MAPPING LOAD City, '/'&City&'\' as F2
order by CityLen desc;
DROP TABLE TMP;
If(MappedCount,TextBetween(Mapped,'/','\',iterno()),'No City Found') as City
WHILE MappedCount >= iterno() or iterno()=1;
SubstringCount(Mapsubstring('MAP', TravelSummary),'/') as MappedCount,
Mapsubstring('MAP', TravelSummary) as Mapped