topic Re: Lookup in QlikView

Lookup

Tue, 29 May 2012 18:23:45 GMT

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,

Lookup

swuehl — Tue, 29 May 2012 19:25:53 GMT

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

Re: Lookup

Tue, 29 May 2012 20:09:22 GMT

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.

Re: Lookup

swuehl — Tue, 29 May 2012 21:12:18 GMT

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

Re: Lookup

Wed, 30 May 2012 13:22:52 GMT

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

Re: Lookup

swuehl — Wed, 30 May 2012 13:35:46 GMT

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

Re: Lookup

Wed, 30 May 2012 14:58:13 GMT

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.

Re: Lookup

Wed, 30 May 2012 15:00:48 GMT

Can you please explain this formula.

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

Thank You!

Re: Lookup

swuehl — Wed, 30 May 2012 17:05:36 GMT

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?