Filter Key Word based on another Key Word

[#1]

I am brand new to Qlikview. I have two tables of data (Table1 and Table2) from one Excel file. Table1 has a field called KeyWord with a string of data for each field (For example, Table1, from cell A1: 5G#;ABI#;CAPLAN#;). Table2 has one column of data and each cell has a unique value (For example, Table2, cell A1 to C1: 5G, ABI, CAPLAN). Each cell in Table2 has 1 value. I'm trying to create a Key Word search function where the user selects a key word from Table2 that pulls up all values in Table1 containing the value selected by the user.

Table1
KeyWords
5G#;ABI#;CAPLAN#;
5G#;ABI#;
ABI#;CAPLAN#;

Table2
KeyWordsMaster
5G
ABI
CAPLAN

RESULT
IF 5G selected in Table2, Table1 displays:
5G#;ABI#;CAPLAN#;
5G#;ABI#;

[marcus_sommer]

Qlik worked on the basis of table-associations and I suggest to use one for your requirement. From your description I could imagine the following:

table1:

load * from table1;

RecordTagsFromTable1:

load KeyWords, subfield(purgechar(KeyWords, '#'), ';') as RecordTag resident table1;

whereby purgechar() removed the hash-sign and subfield splitted the fieldvalue on the semicolon and creates several records from them. After them you could use RecordTag as your search-field.

- Marcus

[#3]

Thanks Marcus,

Introducing a little more complexity, what if my values have numbers associated between the "#" and ";"? For example, 5G;#23;#ABI;#190;#CAPLAN;#122

So the real value of 5G is "5G;#23"

Value of ABI is "ABI;#190"

Value of CAPLAN is "CAPLAN;#122"

it becomes more complicated so I thought it might be easier to have another field (liked table2) the filters table1 because table2 is already cleaned up

[#4]

Hey Marcus, I changed the values making them "5G;#23" etc., as above, and your formula seems to work. Do you know how I can make this a drop-down and remove any blank values?

[#5]

Not sure if my other comment was posted but you solution seems to work even when I change the values, as above, So the real value of 5G is "5G;#23"   Do you know how I can remove blank values?

[marcus_sommer]

Blank values could have two reasons - one is there is no value within the KeyWords and the other is that the used splitting-expressions didn't catch each use-case. The first one could be easily handled with a where-clause and for the second case you would need to modify the expression appropriate assumingly with some if-loop. I mean something like that:

RecordTagsFromTable1:

load KeyWords, if(KeyWords = 'x', 'y', subfield(purgechar(KeyWords, '#'), ';')) as RecordTag

resident table1 where len(trim(KeyWords)) > 0;

A dropdown could be implemented by a multibox or a table-chart for this field but if there is enough space a listbox is more suitable to select values.

- Marcus

[#7]

Marcus, after examining the returned list box, I found the numbers still exist but are separated out. The numbers appear below the text values in the list box. Any way to remove them?

[marcus_sommer]

It's not clear to me if this is related to the challenge above. For me it looked if there are several different fields displayed within listboxes or should this be just one listbox separated into two screenshots?

- Marcus