topic Re: Replacing if with set in QlikView

Replacing if with set

Thu, 14 May 2015 12:53:57 GMT

Hi Guys.

I know how to replace if with set analysis when expression matters only on one variable but how to replace it in this case?

Sum(if(WhStockBalance>=ReservedQty,ReservedQty,WhStockBalance)*UnitCostPrice)

I need to have some fields ignored over selections but i can't figure it out how to convert this expression into set analysis

Thanks

Re: Replacing if with set

alexandros17 — Thu, 14 May 2015 12:58:20 GMT

Set Analysis doesn't execute a contitional evaluation so I think you cannot change your if condition with set analysis ...

Re: Replacing if with set

anbu1984 — Thu, 14 May 2015 12:59:06 GMT

Replace Field_To_Be_Ignored by your field name

Sum({<Field_To_Be_Ignored=>} if(WhStockBalance>=ReservedQty,ReservedQty,WhStockBalance)*UnitCostPrice)

Re: Replacing if with set

Clever_Anjos — Thu, 14 May 2015 12:59:57 GMT

If you need to ignore selections into Field 1

Sum({<Field1=>}if(WhStockBalance>=ReservedQty,ReservedQty,WhStockBalance)*UnitCostPrice)

Re: Replacing if with set

Clever_Anjos — Thu, 14 May 2015 13:01:43 GMT

I think you can use RangeMin in order to replace your IF too

Sum({<Field1=>} Rangemin(WhStockBalance,ReservedQty)*UnitCostPrice)

Re: Replacing if with set

gsbeaton — Thu, 14 May 2015 13:06:55 GMT

Hi Julius,

You're going to have to do something like:

IF(WhStockBalance>=ReservedQty,

SUM(ReservedQty*UnitCostPrice),

SUM(WhStockBalance*UnitCostPrice)

)

A bit more verbose than you wanted, but should work.

George

Re: Replacing if with set

Thu, 14 May 2015 13:10:34 GMT

Wow guys thanks i can ignore now. I will give another question then. Can i combine fields that needed to be ignored and the field value to be selected. For example now i can ignore Field1 and how to set the Field2 value = 1?

Re: Replacing if with set

Clever_Anjos — Thu, 14 May 2015 13:16:22 GMT

Sum({<Field1=,Field2={"1"}>} Rangemin(WhStockBalance,ReservedQty)*UnitCostPrice)

Re: Replacing if with set

gsbeaton — Thu, 14 May 2015 13:22:49 GMT

Sorry, I missread your original question, to follow with your subsequent question, yes, you can combine fields to be ignored with ones to be respected. You do the following

Sum({<Field1=, Field2=, Field3={'Field to filter on'}>}if(WhStockBalance>=ReservedQty,ReservedQty,WhStockBalance)*UnitCostPrice)