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

客户案例截图:转置-rank-join-聚合

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

老师们辛苦了 ~

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

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

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

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

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

简述步骤如下:

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

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

使用prep的排名功能

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

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

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

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

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

使用Excel建立累计占比

接下来是关键。

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

使用prep的高级匹配

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

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

三、使用聚合减少数据

新建一个聚合节点。

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

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

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

喜乐君

Nov 1, 2020