14 Replies Latest reply: Oct 19, 2016 6:47 AM by Supriya R

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

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}

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Try something like this:

if

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

ExtAmt*.94,

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

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

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

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Thank You Rebecca- The expression did work well.

It leads me to a larger issue, though.

Regards

• ###### Re: Trying to create expression for If..then..elseif..else..end if

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.

• ###### Re: Trying to create expression for If..then..elseif..else..end if

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

• ###### Re: Trying to create expression for If..then..elseif..else..end if

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.

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Many Thanks. Will Post on Monday morning !

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Donald-

Here is a copy of script.

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

Drew

• ###### Re: Trying to create expression for If..then..elseif..else..end if

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:
Code, Factor
I, 0.42
Y, 0.42
Z, 0
];

Fact:
*,
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;
SeriesCode,
ShipToCommPlanID,
etc....
FROM

```

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.

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Thanks Donald.

Small Obstacle:      I am geting this script error mesage:

SQL SELECT ExtAmt,

ItemUserFld3,

ShipTOCommPlanID

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,

ShipTOCommPlanID

FROM MAS500Custom.dbo."WHS_SeriesTen_2012";

Thanks,

Drew

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Drew, you have a few issues here that are going to prevent a successful load.  You are getting the error because of the reference to SeriesCode under "Fact:" but that field is not avaialble in the LOAD/SELECT you do just under.  But beyond that, there are other problems here.

To help you, I need to understand how these two tables (or views) that you are loading relate to each other.  In your custom calculation, you are referencing fields from both of the loads here:

So how do you relate those two entities?

• ###### Re: Trying to create expression for If..then..elseif..else..end if

by the field which I aliased as MajLink.

thats how I linked the 2 tables

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Drew,

I am a bit in the blind without seeing your actual data, but MajLink seems an unlikely key to associate here.  It is in fact one of the fields used in your formula, and it appears to be some sort of short code (i.e., 'YK').  In any case, you know your data so you can apply the concept here as it fits.  There are many ways to do this, but one way follows:

2. left join to this your second table

3. noconcatenate load from this resident table to create your final table

In the last step (3) you can do your calculations as needed.  I've attached a sample to give you the idea.  Be aware that this is just the concept.  You will have to consider your data, the keys you use to associate tables, and which table is primary (order of Joins).

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Donald-

You have gone above and beyond, in helping me.

Your info and suggestions have been really great.

I hope I can repay the favor some day.

Let me know if business brings you to NY so I can buy you a drink.

Best-

Drew

• ###### Re: Trying to create expression for If..then..elseif..else..end if

Hey Donald,

i have a inline statement where i need to load expresion value

MetricsDims:

LOAD Metrics , Replace(Datavalue, '@','\$') INLINE [

Metrics  , Datavalue

Invitations , @(vProg_Last_Campaign_Completion_Count)

Delivered ,  @(vParlege_Last_Campaign_Completion_Count)

];

Currently i am getting like this

Invitations , @(vProg_Last_Campaign_Completion_Count)

Delivered , @(vParlege_Last_Campaign_Completion_Count)

but i need actual values for variables

so that in table i get like this:

Invitations , 3

Delivered ,  4

I know using above code in script to inline statement doesnot work,i have to use pick and match by refering this Expressions in Fields