max and group by in script

[#1]

Hi all,

I have data like the following:

Id	Seg
1	260
1	144
1	555
2	628
2	953
2	876
2	212
3	142
3	123

Load

Id,

Seg

Resident tab1 group by id;

I need to achieve the following result:

Id	Final Seg
1	144
2	953
3	142

where

Final Seg = if(Seg >=140 and Seg <=146, Seg, max(Seg))

Any idea is highly appreciated. Thanks a lot!

[#2]

Hi Yacine,

Here you are a possible solution.

Best regards.

[shumailh]

Try this expression in ur table,

IF(SEG>=140 AND SEG<=146, SUM(SEG), MAX(SEG))

Regards,
Shumail Hussain

[#4]

Hi,

Thanks for your reply. It works but not exactly what I need. I need the max of each Id, not the max of the whole list.

Thanks.

[#5]

Hi ,

Can you explain how do you wanna get the result table then? because I don't understand the approach if you need the max of each Id, by example, for the records ID = 1, if Seg > = 140 and Seg <= 146 then Seg else Max(Seg), how do you get the table result id=1 Final Seg=144 for the three records? 1 - 144, 1 - 260 and 1 - 555?.

Best regards.

[#6]

Hi,

for each Id

if one of the values (Seg) is in the interval [140 - 146] then

finalSeg = Seg

else // no Seg value in the interval

finalSeg = the max Seg value (for this Id)

end

end

Thanks.

[#7 · Accepted solution]

Hi Yacine,

Here you are my possible solution.

I hope it works for you.

I have a doubt, ¿what would happen if you found two values in range for the same Id?

Best regards.