Hello,
I have seen several queries (in our Qlik Community pages) that refer to implementation of Excel-style vLookup function within Qlik Sense.
We can use ApplyMap() function as part of Loading Script, and get desired result, so long as the Mapping and Values are known beforehand.
However, when we have Group-level calculations within the Table that we perform as part of Qlik Expressions, I am unable to find a VLookup Method that would resolve Mapping of Resultant Values. Below is the problem explanation.
Marks:
LOAD * inline [
Class|StudID|Borough|Marks
Class A|Stud1|Manhattan|76
Class A|Stud2|Queens|68
Class A|Stud3|Manhattan|91
Class A|Stud4|Brooklyn|54
Class A|Stud12|Queens|78
Class B|Stud5|Brooklyn|87
Class B|Stud6|Manhattan|64
Class B|Stud7|Bronx|81
Class C|Stud8|Queens|78
Class C|Stud9|Bronx|96
Class C|Stud10|Manhattan|77
Class C|Stud11|Manhattan|93
] (delimiter is '|');
From above data, I need to calculate Average Marks for each class (Class A, Class B and Class C).
This Average calculation should be looked-up in below Grade Average Table.
GroupGrade:
LOAD * inline [
Marks|Grade
>80|A
75 to 80|B
<75|C
] (delimiter is '|');
Using, above GroupGrade, each Student be assigned a Group Grade Rating (A, B or C) as shown below - See Column 6 below (titled "GroupGrade").
| Class |
StudID |
Borough |
Marks |
AveMarks |
GroupGrade |
SchoolRank |
| Class A |
Stud1 |
Manhattan |
76 |
73.4 |
C |
9 |
| Class A |
Stud2 |
Queens |
68 |
73.4 |
C |
10 |
| Class A |
Stud3 |
Manhattan |
91 |
73.4 |
C |
3 |
| Class A |
Stud4 |
Brooklyn |
54 |
73.4 |
C |
12 |
| Class A |
Stud12 |
Queens |
78 |
73.4 |
C |
6 |
| Class B |
Stud5 |
Brooklyn |
87 |
77.3 |
B |
4 |
| Class B |
Stud6 |
Manhattan |
64 |
77.3 |
B |
11 |
| Class B |
Stud7 |
Bronx |
81 |
77.3 |
B |
5 |
| Class C |
Stud8 |
Queens |
78 |
86 |
A |
6 |
| Class C |
Stud9 |
Bronx |
96 |
86 |
A |
1 |
| Class C |
Stud10 |
Manhattan |
77 |
86 |
A |
8 |
| Class C |
Stud11 |
Manhattan |
93 |
86 |
A |
2 |
Qlik Sense report will utilize the Borough information, and compute Class/Borough level Averages and GroupGrades in a similar manner. Therefore, data is required on QlikSense Report both at Summary and Detail Levels.
I have searched quite a bit, but I have not seen an ability within Qlik to do a VLookup functionality after Intermediate calculations are executed. If it was Not a Group-level aggregation, I have learnt the method to introduce Calculation logic with Data Load Editor, by looking at some of the Community Help pages.
If VLookups of Group-level Aggregations with a related Mapping Table, is Not a possibility, within Qlik Sense, could you please let me know, and we should look for other alternatives.
Thanks, in advance.
