Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
jsobrinho
Contributor

Return pick match condiotional

Hi guys,

I need to verify if each line in my pick match is zero it is zero I need to put N/A, else the value. There is another way to getting the return of pick match and set the N/A to zero values? or is better do this for each line, with if statement inside pick match?

1 Solution

Accepted Solutions

Re: Return pick match condiotional

I don't see how to improve performance, using a variable can help with readibility and maintenance, also a parametrized variable can be used, ie:
SET vExpressionName = (Sum({<Dim1={'$1'} ,Num={2}>}Expression1)/
Sum({<Dim1={'$1'} ,Num={10}>}Expression1)-1);

Expression using the variable:
Pick(Match(Dim1,'A','B','C')
,if($(vExpressionName(A))>0
,$(vExpressionName(A))
,'N/A')
,if($(vExpressionName(B))>0
,$(vExpressionName(B))
,'N/A')
,if($(vExpressionName(C))>0
,$(vExpressionName(C))
,'N/A')
)
14 Replies

Re: Return pick match condiotional

Hi, adding one to match can help?:
Pick(Match(...)+1
, 'N/A'
, Expression for Match=1
, Expression for Match=2
...)
jsobrinho
Contributor

Re: Return pick match condiotional

First of all, thank you for your answer..

I'm doing this
Pick(Match(...),
if (Expression for Match=1 is zero, 'N/A',
Expression for Match=1),
if(Expression for Match=2 is zero, 'N/A', Expression for Match=2

...)

I dont know if +1 is solve my problem

Re: Return pick match condiotional

Have you tried? it works

jsobrinho
Contributor

Re: Return pick match condiotional

I want to eliminate the if, inside the pick match, I would like to optimize my pick match

Re: Return pick match condiotional

There is no if in my answer.
jsobrinho
Contributor

Re: Return pick match condiotional

The + 1 is used when one of these combinations doesn't occur Pick(Match(...),
like Pick (match(country,'A','B','C') + 1,
99
In this example, if Country is different from A, B, C will be shown the 99
But my problem is
after this match
Pick (match(Country,'A','B','C')
I need to do other comparations like
Pick (match(Country,'A','B','C'),
if(
sum({<Country={'A'}, LEVEL={'X'}>}sales) >0,
sum({<Country={'A'}, LEVEL={'X'}>}sales) ,'N/A')
.....
I have a lot of option of Country, and I need to verify if this option is zeros or not, with IF inside pick match, exist another way or better to simply this logic for better performance?


Re: Return pick match condiotional

Ok, now I understand, but i don't know otyher way to do that.

Maybe using RangeMax(0, [Expression]) and check the option to supress zero value can work if your table only has one measure.

There is also the alt() function to control no-numeric results but I think it can't work in this case.

MVP
MVP

Re: Return pick match condiotional

I think if you supply the full expression you have, then that will eliminate some guesswork on the part of those trying to help you. Better still, put together and upload a small qvw file with some representative  data (does not need to be real numbers, but the structure should be the same), including the sheet object where you need the expression and details on what output values you want.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jsobrinho
Contributor

Re: Return pick match condiotional

OK, I'm sorry
I attached one example.