Hi, I have a question regarding the usage of maxover function. I saw in the official doc that maxover function belongs to table function, so I’m wondering if it can only be used for a specific table.
Here is my usecase:
I have a s3 file like below:
| date |
rank |
value |
| 2023-08-16 |
1 |
10 |
| 2023-08-16 |
2 |
20 |
| 2023-08-15 |
1 |
10 |
| 2023-08-15 |
2 |
20 |
| 2023-08-15 |
3 |
10 |
| 2023-08-14 |
1 |
10 |
| And I’d like to filter the data and only show the highest ranking row for each date, like below: |
|
|
| date |
rank |
value |
| — |
— |
— |
| 2023-08-16 |
2 |
20 |
| 2023-08-15 |
3 |
10 |
| 2023-08-14 |
1 |
10 |
Is there another way to do it if I can not use the maxover function?
Thanks!
I can create it by using 2 calculated fields and a filter
Rank-2: maxOver(max(Rank), [Date]) ← This field is using to find the max rank on each date
Value-2: ifelse(max(Rank)={Rank-2}, toString(max(Value)),'N/A') ← this field is to assign value for those row with rank value same as max rank. Otherwise, assign N/A as value
Finally, use filter to filter out those row with N/A
1 Like
Hey @royyung thanks for your response. I modified your solution a little bit to fit my use case
max-rank: maxOver(rank, [date], PRE_FILTER)
if-max-rank: ifelse(rank={max-rank}, 1, 0)
My max-rank field is similar to your Rank-2 field, since I feel using the max inside of maxover is redundant(let me know if I misunderstand anything here).
@junbo_hh that’s absolutely fine, the processes even more simple in your solution. I didn’t use PRE_filter, therefore data was aggregated and need to use Max.
Your solution looks great
1 Like