Expression - double if

[sprqlikview]

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 · Accepted solution]

Hi,

not sure I understood, but something like:

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

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

)  )

is what you wish?

S.

[anbu1984]

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

[sprqlikview]

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

[simenkg]

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]

Have solved it like this now. Thanks.