topic Create list box of search terms in QlikView

Create list box of search terms

Tue, 15 May 2012 15:54:24 GMT

I have a data based on some ratings and reviews for which I have all the charts I need.

I'm trying to create a list of common search terms on the review text (instead of everyone having to type in search terms), which people can click on to see all the charts update.

Eg. Assume my data set is a bunch of reviews for a hotel, rating and some other dimensions for which I have created charts.

I want a list box box of industry search terms like clean, service, staff, view, pool, kids, etc... so that my clients can just click on these terms and discover patterns.

Any suggestions on how to do this?

Thanks,

Vijay

Create list box of search terms

rajeshvaswani77 — Tue, 15 May 2012 16:03:40 GMT

Hi Vijay, You could use BookMarks. regards, Rajesh Vaswani

Create list box of search terms

Jason_Michaelides — Tue, 15 May 2012 16:03:40 GMT

Just create a bookmarks bar. Make each set of selections and save as bookmarks. These will be in your bookmarks bar as a drop-down list.

Create list box of search terms

vlad_komarov — Tue, 15 May 2012 16:05:27 GMT

You can create a mapping table with list of pre-defined items which will be connected with your data via one of the fields (like 'Category') and have a list box with these items available for selecetion.

Good luck!

Regards,

Vladimir

Create list box of search terms

Tue, 15 May 2012 16:37:38 GMT

Rajesh/ Jason,

Thanks for your response with the suggestion for Bookmarks. I tried doing it and running into trouble.

With all selections cleared, I went to the Review field box and typed "clean". Then I went to Bookmarks > Add Bookmarks and named it 'clean'.

Then, to test, I cleared my selections and tried to select the bookmark again hoping that it will apply the search filter correctly, but it failed and did nothing.

Am I doing something wrong?

Valdmir, Thanks for your suggestion. Never tried mapping before..will read about it and give it a shot.

Thanks,

Vijay

Create list box of search terms

Jason_Michaelides — Tue, 15 May 2012 16:41:02 GMT

So "Review" is a field in your dataset and "Clean" is a valid value? Have you tried clicking the value to select it rather than typing it in? Then create the bookmark...

Create list box of search terms

rajeshvaswani77 — Tue, 15 May 2012 16:41:57 GMT

Vijay, Make your selections and then create the bookmark. Once its created then clear selections and select the bookmark. This will apply te selection you made again when you created the bookmark. Thanks, Rajesh Vaswani

Create list box of search terms

rajeshvaswani77 — Tue, 15 May 2012 16:42:38 GMT

Clean is valid and works for me.

Create list box of search terms

vlad_komarov — Tue, 15 May 2012 16:51:45 GMT

Here is the basic example:

WorkModeMap:

mapping LOAD *

INLINE

[

Mode, Description

0, UNKNOWN

10, UNSTAF

20, AVAIL

30, ACD

40, ACW

50, AUX

];

DataLoad:

LOAD *

Inline

[

Mode, Price

0, 12.34

0, 43.24

0, 43.42

10, 34.34

20, 54.52

20, 444.99

30, 3434.34

30, 0.99

30, 2.99

30, 3.62

40, 4.12

50, 4.55

50, 9.99

50, 4.99

50, 33.67

10, 12.00

0, 33.99

];

Data:

Load

Mode,

Price,

ApplyMap('WorkModeMap', Mode) as Category

resident DataLoad;

DROP Table DataLoad;

Use Category field from the last table for selections.

Regards,

Vladimir

Create list box of search terms

Tue, 15 May 2012 20:17:31 GMT

Review is a field that may contain a scentence like "The room was awesome and clean". Another review might say, "The room was clena and the staff were friendly". The values are basically scentences.

When I search for 'clean', it will filter the dataset for reviews that contain the word clean.

For some reason at this point if I set the bookmark, it does not stick. Wondering if bookmark will only store selection and not search values?..will keep trying.

Create list box of search terms

Tue, 15 May 2012 20:35:24 GMT

Okay..I found the problem.

If I search using the quick search on on the field, then that is not bookmarkable.

If I create a seperate search object linking it to my Review field and then use that to search, then it sticks in the bookmark.

So I have an acceptable way that works.

Thanks everyone.

Re: Create list box of search terms

rwunderlich — Tue, 15 May 2012 23:32:48 GMT

Attached is an approach that creates a seperate table of keywords and links them to the reviews.

-Rob

http://robwunderlich.com

Re: Create list box of search terms

rwunderlich — Wed, 16 May 2012 02:54:42 GMT

Here's another version - even simpler. This one works only if the keywords are single words. If the terms are multi words like "poor service" you'll have to use the previous example. Example Qvw attached as well.

Keywords:

LOAD * INLINE [

Keyword

Clean

Pool

Service

Kids

Vacation

]

;

Reviews:

LOAD *, RecNo() as ReviewId INLINE [

Review

The pool was great

Great place for kids. Very clean.

The staff was terrible. Bad stay all around.

Very clean pool. Great service!

]

;

Inner Join(Keywords)

LOAD

ReviewId,

subfield(Capitalize(purgechar(Review, '.!,')), ' ') as Keyword

RESIDENT Reviews

;

Re: Create list box of search terms

Thu, 20 Dec 2012 10:54:00 GMT

Hi Rob

Can you please upload PE enabled version?

SAK

Re: Create list box of search terms

rwunderlich — Thu, 20 Dec 2012 16:36:33 GMT

I'm sorry, I can't PE enable documents. Only Qliktech can do that.

-Rob

Re: Create list box of search terms

Thu, 20 Dec 2012 16:59:19 GMT

Hi Rob,

Can you please paste here script for generating multi word keyword list

SAK

Re: Create list box of search terms

rwunderlich — Thu, 20 Dec 2012 21:01:26 GMT

Here you go:

Keywords:

LOAD * INLINE [

Keyword

Clean

Pool

Service

Kids

]

;

KeywordMap:

MAPPING LOAD

Upper(Keyword),

'|' & Keyword & '|'

Resident Keywords

;

Reviews:

LOAD *, RecNo() as ReviewId INLINE [

Review

The pool was great

Great place for kids. Very clean.

The staff was terrible. Bad stay all around.

Very clean pool. Great service!

]

;

Terms:

LOAD

ReviewId,

subfield(MapSubString('KeywordMap', upper(Review)), '|') as Keyword

RESIDENT Reviews

;

Inner Join(Keywords)

LOAD *

RESIDENT Terms

;

DROP Table Terms;

Re: Create list box of search terms

Fri, 21 Dec 2012 03:48:25 GMT

Hi Rob,

Thanks a lot! I found many selfless people here who are just share knowledge especially with beginners and non-tech people.

Hope I would be able to use your script for my purpose.

BR,

SAK

Re: Create list box of search terms

Tue, 12 Feb 2013 09:09:38 GMT

Dear Rob,

This solution looks perfect to me, and I would like to use this from an excel load, but I cannot seem to change the script from the inline into an excel-load.

-->

Reviews:

LOAD *, RecNo() as ReviewId INLINE [

Review

The pool was great

Great place for kids. Very clean.

The staff was terrible. Bad stay all around.

Very clean pool. Great service!

Where "Review" is a column in the excel.

Re: Create list box of search terms

rwunderlich — Thu, 14 Feb 2013 00:35:02 GMT

I would think it would be as simple as something like:

LOAD Review, RecNo() as ReviewId

FROM

[..\myexcel.xlsx]

(ooxml, no labels, table is Sheet1);

-Rob