Trying to create expression for If..then..elseif..else..end if

[#1]

I'm new to Qlik. I am trying to create this chart expression, and I am having difficulty.

I am trying to apply formulas to the total if these conditions are met. I am pretty inexperienced with syntax for sql, qlik.

Can anyone lend a hand?

Thanks- DM

if {SeriesCode} = "S" and {ShipToAddrState} = "CA" and ({ShipTOCommPlanID} <> "1726" and {ShipTOCommPlanID} <> "1727")

then {ExtAmt} * (.94)

else if {SeriesCode} = "I" then {ExtAmt}* (.42)

else if { MajLink} = "YK" then {ExtAmt} * (.39)

else if {SeriesCode} = "Y" then {ExtAmt} * (.42)

else if {SeriesCode} = "Z" then {ExtAmt} * (0)

else {ExtAmt}

[#2]

Try something like this:

if

(SeriesCode='S' and ShipToAddrState='CA' and ShipTOCommPlanID<>'1726' and ShipTOCommPlanID<>'1727',

ExtAmt*.94,

if(SeriesCode='I',ExtAmt*.42,

if(MajLink='YK',ExtAmt*.39,

if(SeriesCode='Y',ExtAmt*0,

if(SeriesCode='Z',ExtAmt*0,ExtAmt)))))

[#3]

The expression Rebecca posted will work nicely.

With such a complex (or just long) expression, you may want to consider some options to simplify things for your chart expression.  You could perhaps put some of this logic in the load script.  Or for this expression, since the multiplication factor seems tied to the [SeriesCode], you could load a table in the model that includes an assoication (i.e., a Series table), or you could combine these approaches using ApplyMap.

[#4]

Thanks Donald, Rebecca's expression does work nicely. 

I am working with it to try how and where to apply it correctly.

I would love to put this logic into the load script or,

load a table in the model that includes an assiciation (i.e., a Series table),

or combine the approaches using an ApplyMap.

I will continue to research these 3 options,  Could you further assist?

Drew

[#5]

Thank You Rebecca- The expression did work well. 

It leads me to a larger issue, though.

Regards

[#6]

Drew, I'm happy to try to help.  Any way you could post your app or data model so that I can see what you're working with?  I can guess at what you have and offer some ideas, but obviously it would be easier (and more meaningful) to work with your data.

[#7]

Many Thanks. Will Post on Monday morning !

[#8]

Donald-

Here is a copy of script.

Im grateful for any ideas to incorporate the expressions into my script.

Drew

[#9]

While it is always a judgment call as to whether you include logic like this as expressions in your charts or in the load script, sometimes you may find it useful to put some of the complexity in the load script, thus simplifying your charts.  In any case, just some food for thought.  In your expression, the logic is a bit complex, but even so it may be a bit simplified if you setup a mapping table to handle Series Code I, Y, and Z, then you could add an expression to your load table like this:

SeriesMap:
Mapping Load * Inline [
Code, Factor
I, 0.42
Y, 0.42
Z, 0
];
Fact:
LOAD 
          *,
          IF(SeriesCode='S' And ShipToCommPlanID <> '1726' And ShipToCommPlanID <> '1727' And ShipToAddrState = 'CA', ExtAmt * 0.94,
                    IF( MajLink = 'YK', ExtAmt * 0.39, 
                              ExtAmt * ApplyMap( 'SeriesMap', SeriesCode, 1 )
                    )
          ) As CustomCalc;
LOAD 
           SeriesCode, 
     ShipToAddrState, 
     ShipToCommPlanID, 
     MajLink, 
     etc....
FROM
(your data source);

Here I've just used a preceding load for illustration, but you could add such an expression in your main LOAD as needed.

I've modified the inline IF so that we can leverage a mapping lookup table, and in the event the Series Code is not found in the map, then the factor used will simply be 1.

Hope that helps to give you some ideas, in any case.

[#10]

Thanks Donald.

Very Helpful and Informative.

Small Obstacle:      I am geting this script error mesage:

Field not found - <SeriesCode>

SQL SELECT ExtAmt,

    ItemUserFld3,

    ShipToAddrState,

    ShipTOCommPlanID

FROM "whs_mas500_app".dbo.vdvInvoiceLineSadlier

Do you think it could have something to do with the fact that I am trying to pull from 2 diffrnt locations?

SeriesCode

FROM MAS500Custom.dbo."WHS_SeriesTen_2012";

AND

ExtAmt,

ItemUserFld3,

ShipToAddrState,

ShipTOCommPlanID

FROM MAS500Custom.dbo."WHS_SeriesTen_2012";

Thanks,

Drew