Max value asof date across groups

[leonbaum2]

Hi,I am trying to get the max value of a column at every date acrossgroups. Missing values on that date should be carried forward by group.For example, given the table t:t: ([] d:`date$(); x:`int$(); y:`float$())t ,: (2013.01.01; 2; 1.23)t ,: (2013.01.01; 1; 1.13)t ,: (2013.01.02; 3; 1.14)t ,: (2013.01.02; 1; 1.34)t ,: (2013.01.03; 1; 1.04)q)td x y -----------------2013.01.01 2 1.232013.01.01 1 1.132013.01.02 3 1.142013.01.02 1 1.342013.01.03 1 1.04I want to produce the table r:q)rd argmax_x| max_y-------------------| -----2013.01.01 2 | 1.23 2013.01.02 1 | 1.34 2013.01.03 2 | 1.23 I can imagine a convoluted approach using pivot tables, but there mustbe a more clever method. Any suggestions?Thanks,Leon

[manni_patel]

fby

http://code.kx.com/wiki/Reference/fby

select date,x,y where y=(max;y) fby date

[leonbaum2]

Thanks. I think the solution will definitely involve fby, but it's still
missing the inclusion of the most recent value of "y" for groups that
don't have a record on a given day.

For example, on 2013.01.3, the max should be (2013.01.01; 2; 1.23)
because there were no changes to group x=2 since then.

Leon

Manish Patel writes:

> fby
>
> http://code.kx.com/wiki/Reference/fby
>
> select date,x,y where y=(max;y) fby date
>
> On 20 Jan 2013, at 16:34, Leon Baum wrote:
>
> Hi,
>
> I am trying to get the max value of a column at every date across
> groups. Missing values on that date should be carried forward by group.
>
> For example, given the table t:
>
> t: ([] d:`date$(); x:`int$(); y:`float$())
> t ,: (2013.01.01; 2; 1.23)
> t ,: (2013.01.01; 1; 1.13)
> t ,: (2013.01.02; 3; 1.14)
> t ,: (2013.01.02; 1; 1.34)
> t ,: (2013.01.03; 1; 1.04)
>
> q)t
> d x y
> -----------------
> 2013.01.01 2 1.23
> 2013.01.01 1 1.13
> 2013.01.02 3 1.14
> 2013.01.02 1 1.34
> 2013.01.03 1 1.04
>
> I want to produce the table r:
>
> q)r
> d argmax_x| max_y
> -------------------| -----
> 2013.01.01 2 | 1.23
> 2013.01.02 1 | 1.34
> 2013.01.03 2 | 1.23
>
> I can imagine a convoluted approach using pivot tables, but there must
> be a more clever method. Any suggestions?
>
> Thanks,
> Leon
>
> --
> You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers"
> group.
> To post to this group, send email to personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
> To post to this group, send email to personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.

[leonbaum2]

Ok, I think I got in now by also doing an asof join on a cross product
table:

t1: flip `d`x ! flip (distinct t.d) cross (distinct t.x)
select from aj[`x`d;t1;t] where y=(max; y) fby d

d x y
-----------------
2013.01.01 2 1.23
2013.01.02 1 1.34
2013.01.03 2 1.23

Leon

Manish Patel writes:

> fby
>
> http://code.kx.com/wiki/Reference/fby
>
> select date,x,y where y=(max;y) fby date
>
> On 20 Jan 2013, at 16:34, Leon Baum wrote:
>
> Hi,
>
> I am trying to get the max value of a column at every date across
> groups. Missing values on that date should be carried forward by group.
>
> For example, given the table t:
>
> t: ([] d:`date$(); x:`int$(); y:`float$())
> t ,: (2013.01.01; 2; 1.23)
> t ,: (2013.01.01; 1; 1.13)
> t ,: (2013.01.02; 3; 1.14)
> t ,: (2013.01.02; 1; 1.34)
> t ,: (2013.01.03; 1; 1.04)
>
> q)t
> d x y
> -----------------
> 2013.01.01 2 1.23
> 2013.01.01 1 1.13
> 2013.01.02 3 1.14
> 2013.01.02 1 1.34
> 2013.01.03 1 1.04
>
> I want to produce the table r:
>
> q)r
> d argmax_x| max_y
> -------------------| -----
> 2013.01.01 2 | 1.23
> 2013.01.02 1 | 1.34
> 2013.01.03 2 | 1.23
>
> I can imagine a convoluted approach using pivot tables, but there must
> be a more clever method. Any suggestions?
>
> Thanks,
> Leon
>
> --
> You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers"
> group.
> To post to this group, send email to personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
> To post to this group, send email to personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.