topic Need help with an even more complex nested IF statement in QlikView

Need help with an even more complex nested IF statement

Wed, 20 Aug 2014 15:49:29 GMT

Thought I could pull this off with what Ram just helped me with, but this one had a greater number of outcomes and not sure which to approach first.

The result column I am populating is called Excess_Allocation.

The columns I am using for this are:

Flag

Excess

Allowance

Fees

The logic goes like this:

IF Flag = ' '

IF Fees > Allowance

AND SUM(Excess) > 0

AND SUM(Excess) > Fees - Allowance

THEN Excess_Allocation = Fees - Allowance

IF Fees > Allowance

AND SUM(Excess) > 0

AND SUM(Excess) < Fees - Allowance

THEN Excess_Allocation = SUM(Excess) * (Fees - Allowance) / (SUM(Fees) - SUM(Allowance))

IF Fees < Allowance

THEN Excess_Allocation = 0

IF Flag = 'x'

THEN Excess_Allocation = 0

Thanks for your help!

Wed, 20 Aug 2014 15:58:06 GMT

David,

Where you are trying to do at Script level or at the Expression, Can you also give me the sample data file. So i can work and get back to you.

-Ram

Wed, 20 Aug 2014 16:09:06 GMT

Its an Expression

The columns all hold numbers really, except Flag, which uses either an x or a ''.

Wed, 20 Aug 2014 16:20:49 GMT

David,

Can you try this

if(Flag='',

if(Fees > Allowance and sum(Excess) > 0 and sum(Excess) > (Fees-Allowance),Fees-Allowance,

if(Fees > Allowance and sum(Excess)>0 and sum(Excess) < (Fees-Allowance), ((sum(Excess)*Fees-Allowance)/ (sum(Fees)-sum(Allowance))),

if (Fees < Allowance, 0)),0),0)

Not sure whether this will work, but with tweaks with the braces you can make it work.

-Ram

Wed, 20 Aug 2014 18:32:31 GMT

Ok I will give it a go many thanks