Tableau Prep高级业务案例：转置、排序、范围匹配、聚合

本文使用Prep的三个高级功能：prep排序、小于等于范围匹配、通过聚合取顶部数据。同时使用了转置pivot、筛选等功能。

问题背景：我的一家教育客户在购买Tableau之后，要为高中学校提供自动阅卷后的成绩核算与可视化，但是卡在了“赋分”环节。即，按照成绩的百分位排序，将原始成绩调整为不同等级的等级。（为了减少排名对学生的影响，但又不能不排名，因此通过赋分，把某个阶段的学生成绩调整为同一个数值，按照调整后的成绩作为评价依据）。

老师们辛苦了 ～

比如下图为化学成绩，在百分位0.00105042之前的考生（可能原始成绩为94分、95分）调整为100分满分；之后第二个等级0.005252101的考生调整为97分，以此类推。

涉及到百分位的匹配，各个等级又不是等分的，等级过多，很明显不能使用desktop来完成。只能借助于Prep。有三个关键点：

1、prep 2020.1版本提供了高级函数rank，可以直接在某个区域内计算百分位；

2、由于匹配不是一对一的，而是范围匹配，因此需要用到“不等于”或者“大于等于”这样的范围匹配；

3、范围匹配会出现重复，还需要使用聚合功能保留范围内的首行，比如第一个分位数。

简述步骤如下：

一、使用prep的排序功能转化百分位

客户提供的数据是每位学生一行，分别是语文、数学、化学等等，因此先做数据转置。再做处理。

2020.1的Prep提供了fixed LOD、排序等高级功能，相当于把desktop中的高级函数复制到了prep，非常之棒！

这里基于成绩创建排名，排名仅在每个学科内有效，因此选择分组依据为“学科”——对应desktop表计算中的“分区”；然后计算选择“百分位排名”。这样成绩最高的98分对应的是0，第二个成绩95分对应的是0.000166…….

注意，这个排名是累计排名，默认升序，因此最后一名的百分位是1.

二、使用高级范围匹配建立对应关系

由于Prep没有running-sum这样的移动汇总功能，所以在Excel中，为每个级别建立累计占比，第一名是0，最后一名就是1.如下图

接下来是关键。

使用Join建立匹配，注意这里是学科与学科匹配，上面使用prep计算的学科内百分位与Excel中累计占比匹配，匹配方式为“<=”（小于等于）。

注意，这样的范围匹配一定会出现重复。因为成绩最好的成绩（比如百分位0.01），不仅满足第一个等级（比如0.005），同时也满足后面的所有等级（比如最后一位成绩对应的累计百分位是1）。这样成绩最好的学生成绩就对应了所有等级。

接下来需要去除多余的匹配。

三、使用聚合减少数据

新建一个聚合节点。

由于最好学生对应了所有累进百分位，只保留第一个百分位因此取“最小值”；不同百分位对应的成绩是从高到低的，因此成绩取“最大值”，就是每个学生所在的累计百分位和对应成绩。

这样，每个学生的成绩就在原始成绩基础上，基于百分位增加了百分位阶段和对应成绩。

数据涉及到真实数据，抱歉不便于共享。

喜乐君

Nov 1, 2020