Custom Filter based on two columns.

[michael_andrews]

I'm wondering if this is possible to do. We have a table with exams. In this table there are two columns in particular I'm wondering about. One is for exam code, basically an nvarchar column they set to ID and exam template (so can be multiple of the same in the table), and another is a voucher id, which is unique per exam.

We want to set up a filter that would essentially say if the code starts with JKO or the voucher id starts with CMED, it is what we consider an "Academic" exam. If it does not, we consider it a Non Academic. How we would write that in sql is essentiall,

SELECT CASE WHEN exam_code LIKE 'JKO%' OR exam_voucher_id LIKE 'CMED%' THEN 'Academic' ELSE 'Non Academic' AS type FROM exam

The problem is, I would like to have a filter within a qlik report where they can select the word "Academic" and it would do the first part of that case. If they select Non Academic, it does a not on the first part of that case.

I know I could write the filter custom, but I'm trying to make this so it would be global across all sheets. Is there a way to do this without creating a new column in our data load script and handling it there?

[sunny_talwar]

May be like this

LOAD If(WildMatch(CASE, 'JKO*') or WildMatch(exam_voucher_id, 'CMED*'), 'Academic', 'Non Academic') as type,

     ....

SQL SELECT ....

FROM ....;

[michael_andrews]

Am I putting that LOAD statement in my data load script then?

[sunny_talwar]

I think so... are you looking for doing something else?

[michael_andrews]

I was wondering if it would be possible outside of the load script, but my guess was probably now

[juraj_misina]

Hello Michael,

you could use expressions

=WildMatch(CASE, 'JKO*') or WildMatch(exam_voucher_id, 'CMED*')

and

=Not(WildMatch(CASE, 'JKO*') or WildMatch(exam_voucher_id, 'CMED*'))

as search expressions on a particular field (maybe CASE?) and create two bookmarks, then use some button extension (try searching on Qlik Branch) to apply those bookmarks.

But Sunny's advice to put it in the load script and create a new field in which you could make selections is more systematic and I would definitely recommend doing that, if possible.