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]

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?

[#3]

"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

[#4]

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

[#5]

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

[berryandcherry6]

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