Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SL2
Contributor III
Contributor III

search specifics words in multiple dimensions

Hi everyone, I need to check multiple specific words in 3 dimensions: [entry purpose], [description], [comments], the list of specific words are: data, hosting, roaming, AWS, Oracle, license, app, iPhone, etc. without using any extension(as my client won't allow to do that), is there a way to create a dropdown list of these words and use it as a filter to find out the relevant records?

this is the sample data, I coloured the list of words in red, (1)  is it possible to create a filter pane having the list of specific words?

SL2_0-1624044259186.png

(2) after the filter pane is created, when I filter on 'data', the result should be looking like

SL2_1-1624044535485.png

can anyone help me on this? Thanks.

 

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

Assuming that your table is named Entry, you can add the following to the load script to create a table with individual words as a field, each tied to an [entry ID].  It makes use of the SubField() function:

Words:
Load SubField([entry purpose],' ') as Word,
'entry purpose' as EntryField,
[entry ID]
Resident Entry;

Load SubField([description],' ') as Word,
'description' as EntryField,
[entry ID]
Resident Entry;

Load SubField([comments],' ') as Word,
'comments' as EntryField,
[entry ID]
Resident Entry;

Then, you can use the field [Word] in your filter pane.  To limit the words that can filter on, you can use the following as the expression for the dimension in the filter pane.

=aggr(Only({$<Word={'data','hosting','roaming','Oracle','license','app','iPhone'}>}Word), Word)

Hope that helps.

View solution in original post

6 Replies
GaryGiles
Specialist
Specialist

Assuming that your table is named Entry, you can add the following to the load script to create a table with individual words as a field, each tied to an [entry ID].  It makes use of the SubField() function:

Words:
Load SubField([entry purpose],' ') as Word,
'entry purpose' as EntryField,
[entry ID]
Resident Entry;

Load SubField([description],' ') as Word,
'description' as EntryField,
[entry ID]
Resident Entry;

Load SubField([comments],' ') as Word,
'comments' as EntryField,
[entry ID]
Resident Entry;

Then, you can use the field [Word] in your filter pane.  To limit the words that can filter on, you can use the following as the expression for the dimension in the filter pane.

=aggr(Only({$<Word={'data','hosting','roaming','Oracle','license','app','iPhone'}>}Word), Word)

Hope that helps.

SL2
Contributor III
Contributor III
Author

Hi Gary, thanks for your reply!

I tried amending the load script but come across error:

SL2_0-1624051911205.png

the load script looks like:

table name is [Sheet1]

SL2_1-1624051963795.png

Could you please suggest how to fix it? the [sheet1] data is exactly of same size as that in my post. Thanks!

 

 

GaryGiles
Specialist
Specialist

It looks like you left out the space between the quotes in the subfield function:

My post was:         Load SubField([entry purpose],' ') as Word,

Yours looks like:  Load SubField([entry purpose],'') as Word,

When I removed the space on my end, I was able to reproduce the error.  

You will need to add the space in each of the load statements.

SL2
Contributor III
Contributor III
Author

Thanks Gary! one more extended question: if the data has 2 languages, English and Turkish, let's say data in Turkish is 'veri', and I want to find out both 'data' and 'veri' in those dimensions but in the filter I only want to show one value displaying as 'data/veri' rather than showing 'data' and 'veri' separately. Is there a way to do that? Thanks!

GaryGiles
Specialist
Specialist

You will want to make this table driven.  A simple example using an inline table, would be to add the following to the load script after the Words table is loaded.

Join (Words)
Load * inline [
Word, WordML
data, data/veri
veir, data/veri
license, license/ruhsat
rushat, license/ruhsat
];

You would need to add 2 rows for each word.  One row for each language with the second value the same combined words for each row.

Then, in your filter object, you could just use WordML, with using the aggr() function in my previous post.  WordML will only contain the words you want to display and should filter the data properly.

SL2
Contributor III
Contributor III
Author

Thanks Gary! It works!