Qliksense how to search for a string input across multiple text columns using Logical OR

[CV2]

I have seen the answer to this attempted across multiple sources, but many of the solutions seem outdated or to simply not work or are Qlikview instead of qliksense such as: https://community.qlik.com/t5/QlikView-App-Dev/Select-all-fields-where-value-occur/td-p/477350. https://community.qlik.com/t5/QlikView-App-Dev/Button-to-select-multiple-fields-with-logical-OR/td-p...

I have several descriptive text fields in my data table. I would like for the ability to search for a string across all 5 fields and filter the data table accordingly if the string is in field1 OR field2 OR field3, etc. I know how to accomplish this via AND (by creating multiple actions) but not OR... My search string is coming from an input box and I have saved the variable value as vInputBox. I believe I need to use an action button, but I am having trouble connecting the dots. Here is what I tried (but I think this may only be applicable to qlikview:

Button -> select values matching search criteria -> '=field1= like ' & chr(39) & '*$(vInputBox)*' & chr(39) & ' or field2 like ' & chr(39) & '*$(vInputBox)*' & chr(39)

I REALLY need help on this so any pointing in the right direction is much appreciated

[marcus_sommer · Accepted solution]

Personally I wouldn't do such an approach else merging the information within a single-field. This could be done with a concatenate-approach (union in sql) by adding an extra source-field to be able to differentiate between them. And further no variables, input-box and actions just simple selections on the field.

[CV2]

Thanks @marcus_sommer ! when you say concatenate, do you means Field1&Field2&Field3? Just curious as you also mention union in Sql which does not feel the same so maybe I am misinterpreting your use of concatenate.

I should have been more clear in my question, but the reason I am using an input box is that later down the road there will be even more data tables with text fields that we will also need to search through at the same time. The tables cannot be joined but they will be linked through a common id. So the input box seemed like the easiest solution to be able to search on the front end across many fields in different linked tables, but it's seeming now that might be tough.

[marcus_sommer]

I didn't meant a string-concatenation of the fields on a record-level like F1 & F2 & F3 else a concatenating of them on a table-level like:

t: load F1 as F, AnyKey, '1' as Source from Source1;
    concatenate(t) load F2 as F, AnyKey, '2' as Source from Source2;
    ...

The aim of it is to develop the data-model in the direction of a star-scheme which means to have one (big and maybe within n steps horizontally (joins + mappings) and vertically (concatenate) merged) fact-table with n surrounding dimension-tables. It's the officially recommended data-model as best compromise in regards to efforts, the needed know how and the performance point of view. It's quite the opposite to relational sql data-bases but Qlik used a column-oriented storage logic on distinct values and an associative data-model in which this kind of logic worked very well.

To make the sense of it a bit more practically - if you have n tables associated with a common id how could you apply dimension-tables to it for dates, products, what ever? A single dimension-table isn't possible because it would create a lot of synthetic keys and/or circular references and by using multiple dimension-tables - for each fact-table a separate ones you couldn't filter or search on single fields against the data-set else you would have n ones. I don't want to say that's not possible to do everything against n fields but the efforts and the created complexity to synchronize all of them will be enormously - without having any benefits.

That's just related to the usability - if it comes to get access to all included data it will become more difficult. This relates to the common case of having missing keys on one/several/all sides of the n tables. Regardless of having associations or applying joins within the script it means always a pointing against NOTHING respectively NULL and you will miss data. Of course there are ways to handle such scenarios by checking all sources against each other and removing invalid data or to populate the missing ones but nothing is easier as just to concatenate the information.

[CV2]

This make sense..Thank you !!! It appears to be working now 🙂