Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;