Elaborate filtering

[#1]

Hi everyone,

Very new to QlikView and a little bit overwhelmed by the quantity of learning resources...

I'm using a table with some test data and I would like to filter the content based on elaborate rules, like:

- field x should be in values v1 or v2 or v3 (like a {$in:[]} in MongoDB)

- field y should start with abc or def (so I guess, using some kind of regex)

So far, I guess that I would implement that with Expressions, but I would appreciate some step by step guide. Any pointer to an existing tutorial would be also welcome of course.

Thanks!

Thomas

[pokassov · Accepted solution]

Hi!

LOAD  * INLINE [

z,x,y,

1, v1, abcasda

2, v2, xyzwqe

3, v3, sdlkjf

];

Expression:

sum({($<y={'abc*'}>+$<y={'xyz*'}>)*$<x={'v1','v2','v3'}>}z)

[#3]

Thanks, this helps greatly!

Is it possible to put that in a Calculated Dimension, so that it has a value of 'OK' for example if the conditions are met? It would make it easy to filter on the rows that meet the condition.

Thomas

[pokassov]

Hi!

Yes. Try this

aggr(expression,dimension1,dimension2,...)

in this case dimensions separate you expression on necessary groups.

expression is sum(...) etc

[#5]

Last question: what if I want to get the {'v1','v2','v3'} value list from some other field and not hard-code it in the expression?

Thanks

Thomas

[pokassov]

Hi!

year={$(=max(year))}

or sum({$<id={'>10'}>}id)

or sum({$<id={$(vVar1)}>}id)

[pokassov]

You can study some example with online calculator:

Set Analysis Wizard for QlikView | qlikblog.at‌

thx Stefan WALTHER

[#8]

Does sum({$<id={$(vVar1)}>}id) work if vVar1 is a field from an other table?

My use-case: Table1 has a field called SalesLocation, Table2 has a field called LocationCode.

I would like to filter and keep all rows from Table1 where the SalesLocation has a value among the LocationCode field.

I tried:

=if(aggr(sum({$<SalesLocation={$(LocationCode)}>}1),InvoiceNumber)>0,'In','Out')

but it doesn't filter properly (I got 'Out' on all rows)

Thanks

[pokassov]

dimension:

InvoiceNumber

expression:

=if(count({$<SalesLocation=p({$} LocationCode)>}SalesLocation)>0,'In','Out')