topic Re: Multiple if expression with greater than and less than in QlikView

Multiple if expression with greater than and less than

patrickruy — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have 2 variables that I want to use in an expression. The variables contains a value and a sign. The sign is < or >.

So I have 4 options:

Option 1: Variabele 1 = > ### and Variabele 2 = > ####

Option 2: Variabele 1 = < ### and Variabele 2 = < ####

Option 3: Variabele 1 = > ### and Variabele 2 = < ####

Option 4: Variabele 1 = < ### and Variabele 2 = > ####

Now I have following expression. The first two options work but not the last two. Where did I go wrong?

= if(left(Variable1,1)= '<' ,if(Sales) < PurgeChar(Variable1,'<')
and left(Variable2,1)= '<' ,if(Field_A < PurgeChar(Variable2,'<')   ,Sales)))

,if(left(Variable1,1)= '>' ,if(Sales) > PurgeChar(Variable1,'>')
and left(Variable2,1)= '>' ,if(Field_A > PurgeChar(Variable2,'>')   ,Sales)))

,if(left(Variable1,1)= '<' ,if(Sales) < PurgeChar(Variable1,'<')
and left(Variable2,1)= '>' ,if( Field_A > PurgeChar(Variable2,'>')   ,Sales)))

,if(left(Variable1,1)= '>' ,if(Sales) > PurgeChar(Variable1,'>')
and left(Variable2,1)= '<' ,if(Field_A < PurgeChar(Variable2,'<')   ,Sales)))
))))

Re: Multiple if expression with greater than and less than

sunny_talwar — Tue, 06 Jun 2017 15:16:02 GMT

Would you be able to share a sample to see the issue?Preparing examples for Upload - Reduction and Data Scrambling

Re: Multiple if expression with greater than and less than

marcus_sommer — Tue, 06 Jun 2017 15:41:59 GMT

It's not quite clear what do you want to do. Each result would be Sales ... Nevertheless I would try to simplify the approach by using the $-sign expansion, for example:

if(Sales) $(=left(Variable1,1)) PurgeChar(Variable1,'<>=') and

Field_A $(=left(Variable1,2)) PurgeChar(Variable2,'<>='), ...

or maybe even with something like this:

if(Sales) $(=Variable1) and Field_A $(=Variable2), ...

- Marcus

Re: Multiple if expression with greater than and less than

patrickruy — Tue, 06 Jun 2017 18:27:38 GMT

I have a table where the end user can fill in (by example) Variable1 ">100000" and variable2 "<10" then it have to show the sales.

But the end user may also fill in Variable1 "<200000" and Variable2 "<5" and then als shows the sales.

Variable1 could be Sales and variable2 could be a certain index.

I hope this is useful. I will tomorrow look at your examples. Thanks.

Re: Multiple if expression with greater than and less than

patrickruy — Tue, 06 Jun 2017 18:28:52 GMT

Oh, the dimension is Customer so it shows sales per customer.