topic Re: Elaborate filtering in QlikView

Elaborate filtering

Mon, 24 Aug 2015 05:25:48 GMT

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

Re: Elaborate filtering

pokassov — Mon, 24 Aug 2015 12:00:05 GMT

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)

Re: Elaborate filtering

Mon, 24 Aug 2015 12:38:32 GMT

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

Re: Elaborate filtering

pokassov — Mon, 24 Aug 2015 12:43:00 GMT

Hi!

Yes. Try this

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

in this case dimensions separate you expression on necessary groups.

expression is sum(...) etc

Re: Elaborate filtering

Tue, 25 Aug 2015 05:06:21 GMT

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

Re: Elaborate filtering

pokassov — Tue, 25 Aug 2015 06:58:29 GMT

Hi!

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

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

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

Re: Elaborate filtering

pokassov — Tue, 25 Aug 2015 07:02:03 GMT

You can study some example with online calculator:

Set Analysis Wizard for QlikView | qlikblog.at‌

thx Stefan WALTHER

Re: Elaborate filtering

Wed, 26 Aug 2015 01:56:53 GMT

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

Re: Elaborate filtering

pokassov — Wed, 26 Aug 2015 10:02:57 GMT

dimension:

InvoiceNumber

expression:

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