Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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
Hi!
Yes. Try this
aggr(expression,dimension1,dimension2,...)
in this case dimensions separate you expression on necessary groups.
expression is sum(...) etc
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
Hi!
year={$(=max(year))}
or sum({$<id={'>10'}>}id)
or sum({$<id={$(vVar1)}>}id)
You can study some example with online calculator:
Set Analysis Wizard for QlikView | qlikblog.at
thx Stefan WALTHER
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
dimension:
InvoiceNumber
expression:
=if(count({$<SalesLocation=p({$} LocationCode)>}SalesLocation)>0,'In','Out')