Complex Nested IF syntax needed..

[Anonymous]

Hi,

I wanted to use this formula in QlikView script but it is throwing syntax error as it is a complex nested IF !!

Please help

Formula:

if (Invoice_Date > 0 ) { (SQ *USP * AR_Exchange_Rate) + Freight_USD }

else { ( Order_Return_Quantity * Unit_Selling_Price*  Return_Exchange_RATE)  + Freight_USD

if (Nvl ( Order_Return_Quantity, 0 )==0) {0} }

[sunny_talwar]

Can you explain in plan language what this if statement is doing?

[Anonymous]

IF invoice date >0 THEN (SQ*USP*Rate + Frght USD)

ELSEIF invoice date < 0  THEN  (ORQ*USP*Rate + Frght USD)

ELSE (invoice date < 0 AND ORQ=0) THEN 0

[Anonymous]

IF invoice date >0 THEN (SQ*USP*Rate + Frght USD)

ELSEIF invoice date < 0  THEN  (ORQ*USP*Rate + Frght USD)

ELSEIF (invoice date < 0 AND ORQ=0) THEN 0

ELSE 0

**Corrected formula/explanation

[sunny_talwar]

May be like this

If([Invoice Date] > 0, (SQ*USP*Rate + Frght USD),

    If([Invoice Date] < 0,

          If(ORQ = 0, 0, (ORQ*USP*Rate + Frght USD))))

But I wonder how can date be less then 0?

[its_anandrjs]

May be this as well

=if (Invoice_Date > 0, ((SQ*USP*AR_Exchange_Rate) + Freight_USD),

if (invoice date < 0, ((Order_Return_Quantity * Unit_Selling_Price*  Return_Exchange_RATE)  + Freight_USD),

if (invoice date < 0 AND ORQ=0,0,0)))

[sunny_talwar]

Anand this if statement will never go to the last part of the if statement because as soon as invoice date < 0, it will go into the second part....

[vinayakg]

Try this,

if (Invoice_Date > 0 ,(SQ *USP * AR_Exchange_Rate + Freight_USD ),if (Invoice_Date < 0,( Order_Return_Quantity * Unit_Selling_Price*  Return_Exchange_RATE + Freight_USD),if(Invoice_Date <0 and Order_Return_Quantity=0, 0)))

[Anonymous]

Less 0 means when the date column is null.

[its_anandrjs]

Yes right then may be

=if(Invoice_Date > 0, ((SQ*USP*AR_Exchange_Rate) + Freight_USD),

  if(Invoice_Date < 0 AND ORQ = 0,0,

  if(Invoice_Date < 0,((Order_Return_Quantity * Unit_Selling_Price * Return_Exchange_RATE)  + Freight_USD),4)))