topic Re: Return pick match condiotional in QlikView

Return pick match condiotional

jsobrinho — Sat, 16 Nov 2024 03:33:19 GMT

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?

Re: Return pick match condiotional

rubenmarin — Thu, 16 May 2019 19:21:34 GMT

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

Re: Return pick match condiotional

jsobrinho — Fri, 17 May 2019 07:49:01 GMT

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

rubenmarin — Fri, 17 May 2019 08:27:47 GMT

Have you tried? it works

Re: Return pick match condiotional

jsobrinho — Tue, 21 May 2019 09:08:09 GMT

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

Re: Return pick match condiotional

rubenmarin — Tue, 21 May 2019 09:09:18 GMT

There is no if in my answer.

Re: Return pick match condiotional

jsobrinho — Thu, 23 May 2019 21:44:10 GMT

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

rubenmarin — Fri, 24 May 2019 06:38:10 GMT

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.

Re: Return pick match condiotional

jonathandienst — Fri, 24 May 2019 06:57:27 GMT

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.

Re: Return pick match condiotional

jsobrinho — Fri, 24 May 2019 09:07:01 GMT

OK, I'm sorry
I attached one example.

Re: Return pick match condiotional

rubenmarin — Fri, 24 May 2019 12:27:43 GMT

I don't see other way to do it, in particular if the expression to evaluate if it's below zero is different than the one to do when is over zero.

Re: Return pick match condiotional

jsobrinho — Fri, 24 May 2019 12:48:36 GMT

I'm sorry, I put expression wrong, so to evaluate if it's below zero is equal

Pick(
Match(Dim1,'A','B','C'),
if(
Sum({<Dim1={'A'} ,Num={2}>}Expression1)/
Sum({<Dim1={'A'} ,Num={10}>}Expression1)-1>0,
Sum({<Dim1={'A'} ,Num={2}>}Expression1)/
Sum({<Dim1={'A'} ,Num={10}>}Expression1)-1,'N/A')
,

if(
Sum({<Dim1={'B'} ,Num={2}>}Expression1)/
Sum({<Dim1={'B'} ,Num={10}>}Expression1)-1>0,
Sum({<Dim1={'B'} ,Num={2}>}Expression1)/
Sum({<Dim1={'B'} ,Num={10}>}Expression1)-1,'N/A')
,

if(
Sum({<Dim1={'C'} ,Num={2}>}Expression1)/
Sum({<Dim1={'C'} ,Num={10}>}Expression1)-1>0,
Sum({<Dim1={'C'} ,Num={2}>}Expression1)/
Sum({<Dim1={'C'} ,Num={10}>}Expression1)-1
,'N/A')

)

Re: Return pick match condiotional

rubenmarin — Mon, 27 May 2019 09:02:38 GMT

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')
)

Re: Return pick match condiotional

jsobrinho — Mon, 27 May 2019 17:14:07 GMT

Thank you,

Two questions:
1- What is different of creating a variable in the script by SET, and create in CTRL + ALT + V? I tried to create by CTRL + ALT + V, but I can't.

2- Can I pass variable as measure? like:
SET vExpressionName = (Sum({<Dim1={'$1'} ,Num={2}>} $2)/
Sum({<Dim1={'$1'} ,Num={10}>} $2)-1);

$2 = Expression1 or Expressions2

I tried but I can't.

Re: Return pick match condiotional

rubenmarin — Mon, 27 May 2019 18:36:48 GMT

1- You should be able to create them using CTRL + ALT + V, also from an input box andfrom document properties -> Variables

2- Yes, you can pass variable as measure, note that commas are usd as parameter separator so you measure shouldn't have any comma, ie you can use this:
$(vExpressionName(A, Sales))

This expands as:
(Sum({<Dim1={'A'} ,Num={2}>} Sales)/
Sum({<Dim1={'A'} ,Num={10}>} Sales)-1)