Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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}
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?
"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
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).
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
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