topic max and group by in script in QlikView

max and group by in script

Fri, 20 Nov 2009 18:12:15 GMT

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!

max and group by in script

Fri, 20 Nov 2009 18:48:53 GMT

Hi Yacine,

Here you are a possible solution.

Best regards.

max and group by in script

shumailh — Fri, 20 Nov 2009 19:06:08 GMT

Try this expression in ur table,


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

Regards,
Shumail Hussain

max and group by in script

Fri, 20 Nov 2009 19:58:08 GMT

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.

max and group by in script

Fri, 20 Nov 2009 20:26:35 GMT

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.

Re. :Re: max and group by in script

Fri, 20 Nov 2009 20:42:53 GMT

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

Thanks.

Re. :Re: max and group by in script

Mon, 23 Nov 2009 15:47:16 GMT

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.