topic Re: Complex Nested IF syntax needed.. in QlikView

Complex Nested IF syntax needed..

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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} }

Re: Complex Nested IF syntax needed..

sunny_talwar — Mon, 16 Oct 2017 12:48:18 GMT

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

Re: Complex Nested IF syntax needed..

Anonymous — Mon, 16 Oct 2017 12:57:32 GMT

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

Re: Complex Nested IF syntax needed..

Anonymous — Mon, 16 Oct 2017 12:59:19 GMT

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

Re: Complex Nested IF syntax needed..

sunny_talwar — Mon, 16 Oct 2017 13:01:59 GMT

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?

Re: Complex Nested IF syntax needed..

its_anandrjs — Mon, 16 Oct 2017 13:08:19 GMT

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)))

Re: Complex Nested IF syntax needed..

sunny_talwar — Mon, 16 Oct 2017 13:12:19 GMT

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....

Re: Complex Nested IF syntax needed..

vinayakg — Mon, 16 Oct 2017 13:13:49 GMT

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)))

Re: Complex Nested IF syntax needed..

Anonymous — Tue, 17 Oct 2017 03:45:45 GMT

Less 0 means when the date column is null.

Re: Complex Nested IF syntax needed..

its_anandrjs — Tue, 17 Oct 2017 04:07:23 GMT

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)))

Re: Complex Nested IF syntax needed..

Anonymous — Tue, 17 Oct 2017 12:52:15 GMT

This worked!

Thanks