topic Re: Two separate IF statements in one expression. in QlikView

Two separate IF statements in one expression.

neena123 — Thu, 30 Apr 2015 14:09:13 GMT

I have two separate IF statements I am trying to put into one expression in a straight table. It is only giving me back -1 and nothing else.

My expression is:

=IF([Payment Terms]='12',[Extended Amount]*0.02)

IF([Bill To]='24973618' OR '24973619',[Extended Amount]*0.12)

How can I make this expression work without having to make two separate columns for it. Any help is much appreciated.

Re: Two separate IF statements in one expression.

sunny_talwar — Thu, 30 Apr 2015 14:11:27 GMT

Use a nested if may be. But I need to know the logic before anything can be suggested.

Best,

Sunny

Re: Two separate IF statements in one expression.

rubenmarin — Thu, 30 Apr 2015 14:13:46 GMT

Hi, you can do a nested if, be aware that if the first 'if' is true, the second is not executed:

=If([Payment Terms]='12',[Extended Amount]*0.02,

If([Bill To]='24973618' OR '24973619',[Extended Amount]*0.12))

In case of a [Bill To]='24973618' and also [Payment Terms]='12' it will do "[Extended Amount]*0.02" (the first 'If' it finds as true)

If you want both you can sum them:

=If([Payment Terms]='12',[Extended Amount]*0.02, 0)

+ If([Bill To]='24973618' OR '24973619',[Extended Amount]*0.12, 0)

Re: Two separate IF statements in one expression.

robert_mika — Thu, 30 Apr 2015 14:14:02 GMT

Try:

=IF([Payment Terms]='12' OR [Bill To]='24973618' OR [Bill To]='24973619',[Extended Amount]*0.12)

Re: Two separate IF statements in one expression.

Thu, 30 Apr 2015 14:17:59 GMT

in order to put 2 inside you have to do it like this:

IF(

[Payment Terms]='12', [Extended Amount]*0.02)

IF([Bill To]='24973618' OR '24973619', [Extended Amount]*0.12)

'nothing' ))

Re: Two separate IF statements in one expression.

mato32188 — Thu, 30 Apr 2015 14:26:22 GMT

Hi,

I am not sure, if there are any other "limitations"...

in your load script create two flags.

....

IF(PaymentTerms=12, 0.02, 1) as @FlagA,

IF(BillTo = 24973618 OR BillTo = 24973619, 0.12, 1) as @FlagB

...

then in expression calculate sum(Extended Amount * @FlagA * @FlagB).

Hope helps

Re: Two separate IF statements in one expression.

neena123 — Thu, 30 Apr 2015 14:28:17 GMT

Thanks guys for the fast reply! Marc I tried your approach however now it is calculating that the Payment Terms is true for all the data under the Payment Terms field not just when it equals 12.

Re: Two separate IF statements in one expression.

neena123 — Thu, 30 Apr 2015 14:51:24 GMT

I applied your first expression however it is now calculating everything that doesn't match either Payment Terms=12 and Bill To=24973618' OR '24973619'. It works but I don't need for everything else that doesn't match to be calculated.

Re: Two separate IF statements in one expression.

sunny_talwar — Thu, 30 Apr 2015 14:58:27 GMT

You need to tell us what exactly the logic before we can solve this:

Is it Condition1 or Condition2 or Condition3? Condition1 and (Condition2 or Condition3)?

Re: Two separate IF statements in one expression.

Peter_Cammaert — Thu, 30 Apr 2015 14:58:31 GMT

Martin has a point here. Expressions usually do some kind of aggregation, but I don't see any in your example. Maybe [Extended Amount] is the label of another (hidden) expression column? If not, is this a calculated dimension? If not, this can only work if there is only one data row per combination of dimension values...

Re: Two separate IF statements in one expression.

rubenmarin — Thu, 30 Apr 2015 14:59:47 GMT

I missed something in the 2nd if:

=If([Payment Terms]='12',[Extended Amount]*0.02,

If([Bill To]='24973618' OR [Bill To]='24973619',[Extended Amount]*0.12))

Re: Two separate IF statements in one expression.

neena123 — Thu, 30 Apr 2015 15:04:22 GMT

Thanks everyone for replying and for all your help!