search specifics words in multiple dimensions

[SL2]

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?

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

can anyone help me on this? Thanks.

[GaryGiles · Accepted solution]

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]

Hi Gary, thanks for your reply!

I tried amending the load script but come across error:

the load script looks like:

table name is [Sheet1]

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

[GaryGiles]

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]

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]

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]

Thanks Gary! It works!