VLookup of Computed Group Aggregation columns - Is this possible within Qlik Sense capabilities?

[Rich5678]

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.

[Lisa_P · Accepted solution]

You need to aggregate the Class marks up using a group by function, then use the Interval Match function to compare the result to your ranges as per script below ..

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 '|');

Load Class,
Avg(Marks) as ClassAvg
Resident Marks
Group By Class;

GroupGrade:
LOAD * inline [
From| To|Grade
80| 100 |A
75 | 80|B
0 | 75|C
] (delimiter is '|');

Left Join(GroupGrade)
IntervalMatch(ClassAvg)
Load From, To
Resident GroupGrade;

[marcus_sommer]

If you could do it within the script the provided intervalmatch-logic is a good way to handle such tasks - especially if there are much more values which needs to be grouped.

Beside this you may use a pick(match()) approach - which could be applied within the script and the UI. Both functions belong to the conditional functions and have within Qlik the special behaviour that at first all specified branches are calculated before the TRUE/FALSE evaluation happens. But if they kept short they work usually very performant. In principal it's an nested if-loop but with an easier syntax and handling.

In your case it may look like:

pick(match(-1, avg(Marks) < 75, avg(Marks) <= 80, avg(Marks) > 80), 'C', 'B', 'A')

which is a quite easy way or in a more performant way like:

pick(match(rangemax(74, rangemin(81, round(avg(Marks)))), 74,75,76,77,78,79,80,81),
   'C', 'B', 'B', 'B', 'B', 'B', 'B', 'C')

Essential is here that only one aggregation is needed - but the result must be rounded to a sensible value which is often an integer and the range-functions are just to shorten the result + return list by unifying each value below/above the thresholds.

- Marcus

[vidyutverma]

If you are coming from Excel, you need to get the hang of doing Joins, Thats how in Qlik Scripts you get transformations done based on info available in other tables. 

Similarly in Chart Expressions, learning about how aggr() works will help you with nested subqueries.

[Rich5678]

Thanks very much Lisa,

Simply Brilliant, 

Have a wonderful day..

[Lisa_P · Accepted solution]

To your question, this is part of the intervalmatch function which compares a field to a range of values, in This case From and To to classify your ClassAvg into the correct Grade.

[Rich5678]

Many thanks Lisa_P.
Could you please take a look at the below issue that I raised on a separate thread, while you are at it.

https://community.qlik.com/t5/New-to-Qlik-Sense/Nested-Aggregation-within-Loader-Script/m-p/1966794#...

Many many thanks, in advance

[Rich5678]

@Lisa_P 

Following is an extension of the same issue that I brought to your attention yesterday.

https://community.qlik.com/t5/New-to-Qlik-Sense/Nested-Aggregation-within-Loader-Script/m-p/1966794#...

However, the complication is that I need to calculate an iterative Weighted Value as shown in below formula.

sum(Marks * (FundedAmt / sum(FundedAmt))) as ClassRating

I tried to add a new step for calculating sum(FundedAmt) as a separate calculation, but that did not work either.

Hopefully, if you could get a chance to take a look at that question.

Many thanks, in advance

[Lisa_P · Accepted solution]

For this one, here is one approach:

Marks:
LOAD * inline [
Class|StudID|Marks|FundedAmt
Class A|Stud1|76|672
Class A|Stud2|68|654
Class A|Stud3|91|3453
Class A|Stud4|54|244
Class A|Stud12|78|654
Class B|Stud5|87|665
Class B|Stud6|64|567
Class B|Stud7|81|975
Class C|Stud8|78|979
Class C|Stud9|96|1654
Class C|Stud10|77|345
Class C|Stud11|93|432
] (delimiter is '|');

Funded:
Left Join (Marks)
Load Class,
Sum(FundedAmt) as ClassFundedTotal
Resident Marks
Group By Class;

NewMarks:
Load *,
[Marks*FundWt]/ClassFundedTotal as ClassRating;
Load *,
Marks*FundWt;
Load *,
FundedAmt/ClassFundedTotal as FundWt
Resident Marks;

Drop Table Marks;

[Lisa_P · Accepted solution]

As an explanation, once you have created the aggregation table, you join it to the original.

Then you can create the other fields once they are all in the same layer, the prededing load concept starts from the bottom load then uses that as a source for the statement above, building up layers of transformation.