topic Re: Expression - double if in QlikView

Expression - double if

sprqlikview — Fri, 06 Mar 2015 07:52:27 GMT

I've tried but haven't got the correct result.

I have a quantity unit "BOX" where I need to divide my quantity with 1000 and it works if I use the following expression:

sum ( if (Unit = 'BOX' , Quantity/1000 , Quantity) )

In addition I have two types of transactions, one is the cancellation so I would need to have this negativ. I use the following formula which works.

sum ( if ( Type = '648' , Quantity*-1 , Quantity) )

How can I best combine them?

Thanks

simospa — Fri, 06 Mar 2015 07:55:40 GMT

Hi,

not sure I understood, but something like:

sum ( if (Unit = 'BOX' , Quantity/1000 ,

if ( Type = '648' , Quantity*-1 , Quantity)

) )

is what you wish?

anbu1984 — Fri, 06 Mar 2015 07:56:29 GMT

sum ( if (Unit = 'BOX' , Quantity/1000 ,if ( Type = '648' , Quantity*-1 , Quantity) ))

sprqlikview — Fri, 06 Mar 2015 08:00:22 GMT

Thanks for that. The else statement confused me and I've tried something far to complicated. Appreciate the quick response.

simenkg — Fri, 06 Mar 2015 08:05:14 GMT

Sum(if(...)) is as far from best practice as you can come.

This is much better to do in the script in my opinion.

Load *,

if(Unit = 'BOX', Quantity/1000,if( Type = '648',Quantity*-1,Quantity) as NewQuantity

from Fact....;

Then the expression becomes Sum(NewQuantity)

sprqlikview — Fri, 06 Mar 2015 12:23:02 GMT

Have solved it like this now. Thanks.