Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lookup

I am trying to do a lookup on a specific performance related metric. Each customer service rep receives an average of 5 call scores each month on a 0-100 range. After that, the agent also receives a leveling score on a 1-5 range based on the monthly average of the call score. I am trying to get my script so that whenever I select a range of months, the script does a loookup from an excel spreadsheet. Currently when I select a range of months I am getting an average of the leveling scores rather than receiving a leveling value based on the average of the call scores. I hope my explanation makes sense.

Here is my script:

Quality:

Directory;

LOAD

'' AS Date,

Year & '-' & LEFT(Month,3) AS YearMonth,

Associate AS Rep_Name,

Associate & Year & '-' & LEFT(Month,3) AS RepYearMonth,

(Score)

FROM \\swqlikv1\QLIKVIEW\1_Resources\ContactCenter\CallScoreAssignments.xls (biff, embedded labels, table is [Eval Assignments$])

WHERE Year >= 2010;

Quality1:

Load

YearMonth,

Rep_Name,

RepYearMonth,

Round(Avg(DISTINCT(Score)),-.1) As Quality

Resident Quality

Group By  Rep_Name, YearMonth, RepYearMonth;

Drop Table Quality;

Left Join (Quality1)

LOAD

Round((Quality),-.1) As Quality,

[Quality Level] AS Quality_Level


FROM

Leveling.xlsx

(ooxml, embedded labels, table is Sheet1);

Please let me know if you need any further information.

Thank you,


1 Solution

Accepted Solutions
swuehl
MVP
MVP

Saurav, S

I noticed that your last load

Left Join (Quality1)

LOAD

Round(Quality,-.1) As Quality1,

[Quality Level] AS Quality_Level1

FROM

(ooxml, embedded labels, table is Sheet1);

will create a full Quality / QualityLevel table per existing record (beacuse you renamed the fields and there are no matching fields in the joining table Quality1). Is this what you intend to do?

Looking at your problem, you only have problems with the total line in your table, right?

You could use a variable as suggested above and then use

=if(dimensionality()=0,vTest,avg(DISTINCT([Quality Level])))

to use your variable only for the total line.

If your quality / quality level is not changing day to day (i.e. your table / logic will not be changed by the users in the excel), you could also get rid of the lookup and calculated the quality level on the fly using a dollar sign expansion with arguments:

Define a variable vQL (no equal sign here):

if($1>=89,5,if($1>=85,($1-85)+1,if($1>=83,($1-83)/2,0)))

and then in your expression use:

=$(vQL(avg(Quality)))

See attached for both approaches.

Regards,

Stefan

P.S: Regarding your last post, what do you think this expression does?

View solution in original post

8 Replies
swuehl
MVP
MVP

Saurav S,

I am not sure if I fully understood what your want to achieve. Could you post some sample lines of data together with your expected result?

Lookup() is a script function, so I believe you can't call this function when you make any selections in the front end.

If you are not talking about the QV lookup function, please clarify your setting and requirement.

Regards,

Stefan

Not applicable
Author

Hi Stefan,

I am not certain whether I need to use the lookup function or not. I am sorry if the title of my post was misleading.

I have attached a table of level scores which shows the ranges of the level score, which are correlated with the average quality.

Example:

Jan:

Quality score= 100

Level Score= 5

Feb:

Quality Score =88.50

Level Score = 4.50

Mar:

Quality Score=83.10

Level Score=.05

I would like to be able to get an average of the two and three month quality score and look up that value in the attached table. So in this example, the 3 month quality score would be (100+88.50+83.10)/3= 92.5 and the leveling score would be a 5.  This is the type of logic I am trying to incorporate in my script.

Please see the attached table for further details regarding the call score and the level score ranges.

swuehl
MVP
MVP

Saurav S,

guess you can get your result with different approaches, best is depending the context (where you want to display the result).

I've loaded your table and created some Scores for some Months.

In global context, you can get your Quality Level by using something like

=only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

Here I use a set expression to filter on the Quality with value num(floor(avg(Score),0.1),'#.00')

This looks a bit strange, the num(floor()) is just to match your number format.

In a table, you can achieve similar using

=aggr(if(floor(avg(Score),0.1)= Quality,[Quality Level]),Month,Quality)

See attached for a sample file.

Regards,

Stefan

Not applicable
Author

Stefan,

The number in the text box is exactly what I want but I do not want it to display in a textbox. Is it acceptable to write this in the script? If so, where?

=only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

I have uploaded the script with the actual call scores so this may help. I also changed the month format to Year-Month, because that is the format I need the data.

Thank you for the all of your help thus far.

Saurav


swuehl
MVP
MVP

No, you can't use a set expression in the script (since the data model is not completely built at this time, selecting on field values will not work).

If you don't want to show it in a text box, where do you want to show it?

You could create a variable in Settings->Variable Overview and name it e.g. vTest with definition

=only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

Then use this variable anywhere in your objects (it will just hold the value as calculated and shown in the text object):

=vTest

See also attached.

With regard to your Year-Month, I would suggest using Monthname() function instead, which will still hold the month start date in the numerical representation or maybe

Date(monthstart(Date),'YYYY-MMM') as YearMonth,

if you need a different formatting.

Hope this helps,

Stefan

Not applicable
Author

Hi Stefan,

I think your suggestion works fine for this situation, however, I have a multiple metrics that I need to apply similar logic to. I thought there would be a much simpler solution to this, since I am already getting the monthly levels scores that I want. My logic breaks when I try to get a leveling score for several months. Instead of looking at the ranges for the quality leveling score I am getting an average of the quality leveling scores, which is slightly different.

I have uploaded my script to show you how I am currently scripting this. I will need to apply a similar logic to other metrics as well.

Thanks.

Not applicable
Author

Can you please explain this formula.

=only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

Thank You!

swuehl
MVP
MVP

Saurav, S

I noticed that your last load

Left Join (Quality1)

LOAD

Round(Quality,-.1) As Quality1,

[Quality Level] AS Quality_Level1

FROM

(ooxml, embedded labels, table is Sheet1);

will create a full Quality / QualityLevel table per existing record (beacuse you renamed the fields and there are no matching fields in the joining table Quality1). Is this what you intend to do?

Looking at your problem, you only have problems with the total line in your table, right?

You could use a variable as suggested above and then use

=if(dimensionality()=0,vTest,avg(DISTINCT([Quality Level])))

to use your variable only for the total line.

If your quality / quality level is not changing day to day (i.e. your table / logic will not be changed by the users in the excel), you could also get rid of the lookup and calculated the quality level on the fly using a dollar sign expansion with arguments:

Define a variable vQL (no equal sign here):

if($1>=89,5,if($1>=85,($1-85)+1,if($1>=83,($1-83)/2,0)))

and then in your expression use:

=$(vQL(avg(Quality)))

See attached for both approaches.

Regards,

Stefan

P.S: Regarding your last post, what do you think this expression does?