topic Re: Lookup equivalent value from expression result in QlikView

Lookup equivalent value from expression result

jessica_webb — Thu, 07 Jan 2016 15:45:15 GMT

I have a mapping table in my script which equates points to grades. The table is approx. 100 rows, and starts as follows:

Points	Grade
45	D
46	D
47	D
48	C
49	C

In my qvw, most of my expressions are built using the points (obviously, as I need a numerical value for my calculations). However, in some of my tables, I would like to show the grade equivalent.

I have one table that shows the average of all candidates points against a yearly dimension, e.g.:

Year	Points	Grade
2012	=AVG(Points)
2013	=AVG(Points)
2014	=AVG(Points)

Obviously the value in the Points column changes according to different selections that are made.

How can I show the equivalent grade too?

Thanks for any suggestions.

Re: Lookup equivalent value from expression result

sunny_talwar — Thu, 07 Jan 2016 16:39:26 GMT

Something like the attached?

Re: Lookup equivalent value from expression result

jessica_webb — Thu, 07 Jan 2016 16:51:35 GMT

Hi Sunny,

This seems like a great start. But what if I don't want to include the 'Year' dimension in the expression? Ideally I'd like it to be separate from that as I have a LOT of data, and I can't easily create the inline table that you've used in your example.

So to use your example script, my 'Table' has

Load

Year,

Points,

ApplyMap('Grade_Map', 'Points') as Grade

etc

So if I created a straight table, then I would see what the points/grade equivalent is.

It's where I'm using the 'AVG' function that it all goes wrong.

Any ideas?

Re: Lookup equivalent value from expression result

effinty2112 — Thu, 07 Jan 2016 16:56:53 GMT

Hi Jessica,

I haven't test this expression so it might not work straight out of the box but what about this expression?

=FirstSortedValue({$<Points = {"<=$(=Avg(Points))"} >} Grade, -Points)

Re: Lookup equivalent value from expression result

jessica_webb — Thu, 07 Jan 2016 17:01:38 GMT

Hi Andrew,

Thanks for your help.

It's not working quite as it should - in a couple of instances I get no result at all (I think because the values are averages (i.e. they have decimal numbers), whereas my equivalence table is on whole numbers only).

But on the one instance it does give me a result, it's not what I would expect - e.g. for an average point score of 46, it gives me a result of 'C' (when it should be a 'D').

Re: Lookup equivalent value from expression result

sunny_talwar — Thu, 07 Jan 2016 17:01:48 GMT

Can you may be create a sample which shows us what you are looking to do?

Re: Lookup equivalent value from expression result

effinty2112 — Thu, 07 Jan 2016 17:15:39 GMT

Hello Jessica,

Something's a bit odd. This formula with the inequality hard written as <=46 returns a 'D' with the five lines of your mapping table loaded into my qvw.

=FirstSortedValue({$<Points = {"<=46"} >} Grade, -Points)

=================================================

I've just realised something (I can be very slow). My method won't work on your mapping table as that table will not exist in the data model after the reload.

Sorry!

Re: Lookup equivalent value from expression result

jessica_webb — Fri, 08 Jan 2016 07:53:17 GMT

Got you - no worries at all. Thanks for the suggestions anyway!

Re: Lookup equivalent value from expression result

jessica_webb — Fri, 08 Jan 2016 07:57:51 GMT

Hi Sunny,

Unfortunately under a bit of time pressure so haven't managed to create a sample file.

As a temporary fix for now (so that I can send a demo to our client later today), I have used a very convoluted 'if' statement, which does work - but probably isn't great for overall efficiency!

IF(Avg(Points)>=89,'E9',IF(Avg(Points)>=88,'E8',IF(Avg(Points)>=87,'E7',IF(Avg(Points)>=86,'E6',IF(Avg(Points)>=85,'E5',IF(Avg(Points)>=84,'E4',IF(Avg(Points)>=83,'E3',IF(Avg(Points)>=82,'E2',IF(Avg(Points)>=81,'E1',IF(Avg(Points)>=80,'D9',IF(Avg(Points)>=79,'D8',IF(Avg(Points)>=78,'D7',IF(Avg(Points)>=77,'D6',IF(Avg(Points)>=76,'D5',IF(Avg(Points)>=75,'D4',IF(Avg(Points)>=74,'D3',IF(Avg(Points)>=73,'D2',IF(Avg(Points)>=72,'D1',IF(Avg(Points)>=71,'C9',IF(Avg(Points)>=70,'C8',IF(Avg(Points)>=69,'C7',IF(Avg(Points)>=68,'C6',IF(Avg(Points)>=67,'C5',IF(Avg(Points)>=66,'C4',IF(Avg(Points)>=65,'C3',IF(Avg(Points)>=64,'C2',IF(Avg(Points)>=63,'C1',IF(Avg(Points)>=62,'B9',IF(Avg(Points)>=61,'B8',IF(Avg(Points)>=60,'B7',IF(Avg(Points)>=59,'B6',IF(Avg(Points)>=58,'B5',IF(Avg(Points)>=57,'B4',IF(Avg(Points)>=56,'B3',IF(Avg(Points)>=55,'B2',IF(Avg(Points)>=54,'B1',IF(Avg(Points)>=53,'A9',IF(Avg(Points)>=52,'A8',IF(Avg(Points)>=51,'A7',IF(Avg(Points)>=50,'A6',IF(Avg(Points)>=49,'A5',IF(Avg(Points)>=48,'A4',IF(Avg(Points)>=47,'A3',IF(Avg(Points)>=46,'A2',IF(Avg(Points)>=45,'A1',IF(Avg(Points)>=44,'P8.9',IF(Avg(Points)>=43,'P8.8',IF(Avg(Points)>=42,'P8.7',IF(Avg(Points)>=41,'P8.6',IF(Avg(Points)>=40,'P8.5',IF(Avg(Points)>=39,'P8.4',IF(Avg(Points)>=38,'P8.3',IF(Avg(Points)>=37,'P8.2',IF(Avg(Points)>=36,'P8.1',IF(Avg(Points)>=35,'P7.9',IF(Avg(Points)>=34,'P7.8',IF(Avg(Points)>=33,'P7.7',IF(Avg(Points)>=32,'P7.6',IF(Avg(Points)>=31,'P7.5',IF(Avg(Points)>=30,'P7.4',IF(Avg(Points)>=29,'P7.3',IF(Avg(Points)>=28,'P7.2',IF(Avg(Points)>=27,'P7.1',IF(Avg(Points)>=26,'P6.9',IF(Avg(Points)>=25,'P6.8',IF(Avg(Points)>=24,'P6.7',IF(Avg(Points)>=23,'P6.6',IF(Avg(Points)>=22,'P6.5',IF(Avg(Points)>=21,'P6.4',IF(Avg(Points)>=20,'P6.3',IF(Avg(Points)>=19,'P6.2',IF(Avg(Points)>=18,'P6.1',IF(Avg(Points)>=17,'P5.9',IF(Avg(Points)>=16,'P5.8',IF(Avg(Points)>=15,'P5.7',IF(Avg(Points)>=14,'P5.6',IF(Avg(Points)>=13,'P5.5',IF(Avg(Points)>=12,'P5.4',IF(Avg(Points)>=11,'P5.3',IF(Avg(Points)>=10,'P5.2',IF(Avg(Points)>=9,'P5.1',IF(Avg(Points)>=8,'P4.9',IF(Avg(Points)>=7,'P4.8',IF(Avg(Points)>=6,'P4.7',IF(Avg(Points)>=5,'P4.6',IF(Avg(Points)>=4,'P4.5',IF(Avg(Points)>=3,'P4.4',IF(Avg(Points)>=2,'P4.3',IF(Avg(Points)>=1,'P4.2',IF(Avg(Points)>=0,'P4.1'

))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Probably Qlikview's worst expression ever, but it'll have to do for now...