search for values in Multiple columns and filter down.

[manideep78]

Dear Community,

I have a unique situation in Qlikview to search for multiple values in multiple columns. 

I know we have a Search Box Object, but this is little different.

Example Situation:

Column 1 Contains 'A' or 'B' or 'C'

Column 2 Contains  'A' or 'B' or 'C'

Column 3 Contains  'A' or 'B' or 'C'

Column 4 Contains  'A' or 'B' or 'C'

The result should be  either 'A' or 'B' or 'C' presents in any of the columns 1-4.

is it achievable in anyways by using Button action?

Thanks.

[QFabian]

Hi @manideep78 , when you talk about column, i dont know if you are talking about fields in listbox, or fields as dimensions or expressions in charts.

If it is about fields in listo box, i just created a textbox with this :

=
'Col 1 : ' & if(sum(aggr(FindOneOf(FIELD1,'abc'), FIELD1))>0, ' Yes', ' No') & '
Col 2 : ' & if(sum(aggr(FindOneOf(FIELD2,'abc'), FIELD2))>0, ' Yes', ' No') & '
Col 3 : ' & if(sum(aggr(FindOneOf(FIELD3,'abc'), FIELD3))>0, ' Yes', ' No') & '
Col 4 : ' & if(sum(aggr(FindOneOf(FIELD4,'abc'), FIELD4))>0, ' Yes', ' No')

And if you wanted as column in chart, is almost the same, maybe you can reference FIELD with column(n)

[marcus_sommer]

It looked that you have a crosstable data-structure. This is in many cases not really suitable to handle them within the UI - neither in calculations nor in selections. Therefore I suggest a transformation into a "normal" data-structure, see: The Crosstable Load - Qlik Community - 1468083.

- Marcus

[manideep78]

Hi @QFabian 

Thank you for the quick response. 

actually I'm not trying to display anything there. 

All I'm trying to achieve is to find a string in few fields(Col1, Col2, Col3, Col4) and if the string exists in any of the fields then show the results.

In my case, 'A' is one string, 'B' is another string, 'C ' is another........

I hope i'm clear this time.

[manideep78]

Hi @marcus_sommer 

I don't have a cross table. I have a regular table structure with Star Schema.

I'm trying to search for a string in any of the columns (Col1,2,3,4) then display the results in table box.

to be clear, 

input a string and find matches in either of the columns 1 to 4 and then display results in table box.

[QFabian]

Ok, for that try using mixmatch

if(
(mixmatch( Col1,'A','B','C','D') +
mixmatch( Col2,'A','B','C','D') +
mixmatch( Col3,'A','B','C','D') +
mixmatch( Col4,'A','B','C','D'))
> 0, yourresult

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFun...

[manideep78]

Hi @QFabian 

Thank you for Quick turnaround.

I think again i'm not clear here. sorry for confusion.

your solution works, If I wanted to show a value in a text object or a straight table.

But, i want to make them as filters. show only those rows in col1-4 where there is a match in a table box.

[QFabian]

@manideep78 , I created this data :

LOAD * INLINE [
Id, Cat, Col1, Col2, Col3, Col4, Value
1, Cat1, A, B, C, D, 10
2, Cat1, A, X, X, A, 99
3, Cat1, B, X, X, X, 99
4, Cat1, X, X, X, X, 99
5, Cat2, X, X, C, X, 99
6, Cat2, X, X, A, X, 99
7, Cat2, X, X, X, C, 10
8, Cat2, B, X, X, B, 10
];

Then created a straight table chart with Id as dimension and this expression :

sum(
if(
match( Col1,'A','B','C','D') > 0 and
match( Col2,'A','B','C','D') > 0 and
match( Col3,'A','B','C','D') > 0 and
match( Col4,'A','B','C','D') > 0,
Value))

This is the result, hope help you a little

[marcus_sommer]

You may not regard this part of your data as a crosstable-structure because it may not look like the common ones which are usually any period-fields - but it is a crosstable if you want to do what you described.

Therefore I suggest that you consider an appropriate transforming again to avoid complex matchings within the UI. Especially if you don't want to use it as a condition within expressions else as a selection. IMO it's much too complicated without a real added value and especially the intended macro/action-stuff goes mostly in the wrong direction.

Also it doesn't mean that you mandatory need to change your whole datamodel else this 4-columns merging into a single column could be added as an extra dimension-table.

- Marcus