Calculate fractile across dataset

[saurabhk18]

Hi,

My research on the Fractile expression tells me that it requires a field to be grouped by. But what if I want to calculate it across the entire dataset? Let's say I have a table with the marks for 10 students:

ID	Marks
1	99
2	89
3	79
4	88
5	90
6	81
7	67
8	89
9	90
10	68

I wish to calculate the quartile each individual score belongs to. As 67 is the lowest score, it would belong to the first quartile, and 99 being the highest would belong to the fourth. But if I were to use Fractile(Marks, 0.25), Fractile(Marks, 0.5) etc, I would need to apply a group by clause, which wouldn't make sense here.

As a workaround, I can load only the marks in a separate table, define '1' as the ID for each row, and then group by ID to calculate the fractile. I can then use this as a mapping table for the original table. But this is a lengthy approach if you have to calculate fractiles for multiple fields (assuming the table also had IQ, weight etc.). Can you suggest something quicker?

Thanks!

-Saurabh

[sunny_talwar · Accepted solution]

Seems like you are trying to do this in the script. If you are, then you can just do a join to your main table like this

Join (TableName)
LOAD Fractile(Marks, 0.25),
     Fractile(Marks, 0.50),
     Fractile(Marks, 0.75)
Resident TableName;