Solved: Assign a 'fixed' calculation to a column in table - Page 4 - Qlik Community

bha · ‎2018-08-01

Hey there

can anyone point me in the right direction here with adding a column to a table? As per below, its logged/interactions total. The total obviously will change based on the selection, but I need it as the same value in each row, due the the data simply being pasted into a legacy spreadsheet.

Many thanks!

sunny_talwar · ‎2018-08-02

Do you think that could affect things?

I think it is def. having an impact here because the total you expected was 492,876 and total you got with my expression was 388,982 and the difference between the two is 103,894.

They actually come up with a “-“ on the table. Is that how nulls behave in Qlik? I notice now when I click the field it doesn’t do anything

Yup that is def. null then.... can you may be create another field in the script like this

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

and then try this

Sum(Aggr(

if

(

sum(Logged)>= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) > 0 //and at least one interaction

,sum(Logged)//then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //or interactions as normal

),

New_Agent))

bha · ‎2018-08-02

Great. I need to figure out where in my script it should go, its based on SQL views. I just gave it a quick try but will test in the morning now. Thanks again for your help Sunny, have a great rest of day/evening

bha · ‎2018-08-03

grrrrr. I am struggling a bit to work out where to put this is my script to handle the nulls

So it should be name_uq in the script (?) which is the field name, Agent is just the label

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

So first thing I want to understand, Len(Trim(Agent)) = 0, I am surprised the null would return 0, not another null (as it would in sql), is that expected?

second thing, does the load order of table matter in the script?.

This data in its simplest form is coming from 3 views. This is/should be straightforward I guess, but after much tinkering I got the data model working. The views are 2 fact tables I guess, and a link table. Again I want to revisit this when my understanding is better and move some of the logic to Qlik and out of SQL where I am more comfortable

My script is like so:

[ava_HandledByAgent]:

LOAD

[AgentLogin],

//If(Len(Trim(AgentLogin)) = 0, ' ', AgentLogin) as AgentLogin, //or here hmmmmmmmmm does it go here

//if(IsNUll([AgentLogin]), 'TEST', [AgentLogin]) as [AgentLogin] , //or would isnull/work be neater?

Date([avaDate]) AS [ava_HandledByAgent.avaDate],

[Voice] AS [ava_HandledByAgent.Voice],

[Orders] AS [ava_HandledByAgent.Orders],

[Customer_Service] AS [ava_HandledByAgent.Customer_Service],

[Non-Voice] AS [ava_HandledByAgent.Non-Voice],

[avaDate]&[AgentLogin] AS [ava_HandledByAgent.CallDate-avaDate],

[avaDate]&[AgentLogin] AS [CallDate-avaDate];

SQL SELECT "AgentLogin",

"avaDate",

"Voice",

"Orders",

"Customer_Service",

"Non-Voice"

FROM "ava_HandledByAgent";

[vLinkTable]:

LOAD

[magusername], //or/and does it go here which is the 'link table' and joins up with the mag_LoggedbyAgent views

[AvaAgentLogin], //and this one too which links to ava_HandledByAgent

name_uq, //THIS IS A OUR MAIN DIMENSION AND IS UNIQUE IN THE VIEW (primary key in the underlying table

DATE([CallDate]) AS [Calendar_Date-CallDate],

[reportstoname],

[CallDate]&[AvaAgentLogin] AS [vLinkTable.CallDate-avaDate],

[CallDate]&[AvaAgentLogin] AS [CallDate-avaDate],

[CallDate]&[magusername] AS [CallDate-magDate],

exclude,

Notes

;

SQL SELECT "magusername",

"AvaAgentLogin",

"name_uq",

"CallDate",

"reportstoname",

"exclude",

"Notes"

FROM "vLinkTable" WHERE reportstoname is not null; //reports to name is non nullable in staff table

[mag_LoggedbyAgent]:

LOAD

[username], //finally, the 2nd view. Does the order of script matter? This one is working and I sure dont want to break it!!!! Just the damn nulls

Date([magDate]) AS magDate,

[Logged],

[magDate]&[username] AS [CallDate-magDate];

SQL SELECT "username",

"magDate",

"Logged"

FROM "mag_LoggedbyAgent";

@dunn

sunny_talwar · ‎2018-08-03

So it should be name_uq in the script (?) which is the field name, Agent is just the label

If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

Yes, use name_uq or whatever the field name is....

So first thing I want to understand, Len(Trim(Agent)) = 0, I am surprised the null would return 0, not another null (as it would in sql), is that expected?

Len(Trim( for null gives 0 in Qlik... not sure how it work elsewhere... but the reason to use Len(Trim(... is to also check for blank spaces which might look like nulls but they actually are not... in your case.. we know those are null... so you can def use IsNull(name_uq)

second thing, does the load order of table matter in the script?.

It should not matter, but what made you ask this? I might be missing something here and that is why I ask....

In your script... you are doing Len(Trim()) on AgentAction? instead or name_uq? I just want to clarify before we move forward...

bha · ‎2018-08-03

So it should be name_uq in the script (?) which is the field name, Agent is just the label
If(Len(Trim(Agent)) = 0, ' ', Agent) as New_Agent

Yes, use name_uq or whatever the field name is....

Cool

So first thing I want to understand, Len(Trim(Agent)) = 0, I am surprised the null would return 0, not another null (as it would in sql), is that expected?

Len(Trim( for null gives 0 in Qlik... not sure how it work elsewhere... but the reason to use Len(Trim(... is to also check for blank spaces which might look like nulls but they actually are not... in your case.. we know those are null... so you can def use IsNull(name_uq)

yeah in sql len or trim of null is (or should be) null, ansi standard

second thing, does the load order of table matter in the script?.

It should not matter, but what made you ask this? I might be missing something here and that is why I ask....

No real reason, just curious, I need to get out the database mindset, I was just thinking when the nulls might 'appear' (they seem to be more like an EXCEPT clause that causes them, values in one 'table' but not the other

In your script... you are doing Len(Trim()) on AgentAction? instead or name_uq? I just want to clarify before we move forward...

To you mean AgentLogin?

I have commented out the trims and isnulls, the script is as is before we started to handle the nulls

staff (basically a personel table)

[name_uq]

this is the distinct staff name, to link the 2 different other tables by their login

[mag_username]. This links to [username] in [mag_LoggedbyAgent]

[AgentLogin]. This links to [Login] in [ava_HandledByAgent]

Does that clarify things a bit?

sunny_talwar · ‎2018-08-03

I guess the problems is not nulls... but missing values for name_uq... it seems that there might be AgentLogin which are not associated with any mag_username.... what is the relation between name_uq and mag_username? may be you can use this in your denominator?

Sum(Aggr(

if

(

sum(Logged)>= Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //more logged than interactions

AND

Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) > 0 //and at least one interaction

,sum(Logged)//then cap logged figure to interactions

,Sum([ava_HandledByAgent.Voice])+Sum([ava_HandledByAgent.Non-Voice])-Sum([ava_HandledByAgent.Orders]) //or interactions as normal

),

AgentLogin))

bha · ‎2018-08-03

Yes, there arent nulls in any of the source data going in

What you said is correct, there are some 'orphaned' values in either or both of the 2 fact tables if they can be called that.

Unfortunately I am not going to be able to test anymore until Monday now. Your patience and understanding is very much appreciated, hope you have a great weekend

Does this make sense?

bha · ‎2018-08-09

Sunny thanks for all your help last week. I have marked this as the answer as it works when all the data is populated (as you were probably aware)

I didn't quite get it working with the pseudo nulls/orphaned records, I will come back to it...

However I fixed the issue which was causing them which is the better 'fix' for now, as the same data is to be used elsewhere

Your help much appreciated!

All the best

(I do believe I have one more thread to come back on you commented on, regarding filtering on an expression), I will get back onto that as soon as I have a moment