topic Re: Trying to create expression for If..then..elseif..else..end if in QlikView

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

Thu, 21 Feb 2013 22:32:12 GMT

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

Thu, 21 Feb 2013 22:50:58 GMT

Try something like this:

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

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

Fri, 22 Feb 2013 00:35:27 GMT

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

Fri, 22 Feb 2013 14:09:06 GMT

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

Fri, 22 Feb 2013 14:12:54 GMT

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

Fri, 22 Feb 2013 20:57:49 GMT

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

Sat, 23 Feb 2013 19:36:53 GMT

Many Thanks. Will Post on Monday morning !

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

Mon, 25 Feb 2013 13:59:09 GMT

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

Tue, 26 Feb 2013 00:56:11 GMT

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.

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

Tue, 26 Feb 2013 21:11:29 GMT

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

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

Tue, 26 Feb 2013 22:39:27 GMT

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:

"whs_mas500_app".dbo.vdvInvoiceLineSadlier (contains ShipToAddrState, ShipToCommPlanID, ExtAmt)

MAS500Custom.dbo."WHS_SeriesTen_2012" (contains SeriesCode, MajLink)

So how do you relate those two entities?

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

Tue, 26 Feb 2013 22:50:51 GMT

"whs_mas500_app".dbo.vdvInvoiceLineSadlier is related to MAS500Custom.dbo."WHS_SeriesTen_2012"

by the field which I aliased as MajLink.

vdvInvoiceLineSadlier.ItemUserFld3 as MajLink, = "WHS_SeriesTen_2012".Major as MajLink,

thats how I linked the 2 tables

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

Wed, 27 Feb 2013 00:46:26 GMT

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:

1. load your primary table of data

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

Wed, 27 Feb 2013 01:33:09 GMT

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

berryandcherry6 — Wed, 19 Oct 2016 10:46:24 GMT

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

But there i didnt get what is column1: Name. please help me on this