Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sprqlikview
Contributor II
Contributor II

Expression - double if

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

1 Solution

Accepted Solutions
simospa
Partner - Specialist
Partner - Specialist

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.

View solution in original post

5 Replies
simospa
Partner - Specialist
Partner - Specialist

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
Master III
Master III

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

sprqlikview
Contributor II
Contributor II
Author

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

simenkg
Specialist
Specialist

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
Contributor II
Contributor II
Author

Have solved it like this now. Thanks.