Help with join

Received: by 10.101.20.1 with SMTP id x1mr966580ani.8.1256221448412; Thu, 22
Oct 2009 07:24:08 -0700 (PDT)
Date: Thu, 22 Oct 2009 07:24:08 -0700 (PDT)
X-IP: 74.201.4.10
User-Agent: G2/1.0
X-Google-Token: Lj-j_AwAAADgxPiUuPFreisszMLhZj0B
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; GTB6;
.NET CLR 2.0.50727; .NET CLR 1.1.4322; .NET CLR 3.0.4506.2152; .NET CLR
3.5.30729; InfoPath.1; OfficeLiveConnector.1.3; OfficeLivePatch.0.0),gzip(gfe),gzip(gfe)
Message-ID: <3d389077-6afa-417e-bf62-99b174025a30@k26g2000vbp.googlegroups.com>
Subject: Help with join
From: Ramdas
To: “Kdb+ Personal Developers”
X-Google-Approved: charlie@kx.com via web at 2009-10-22 14:27:28

Hi,

I am new to KDB so excuse me..


Can someone help me with the join query as described below..

I have two tables

m:([code:FGHJKMNQUVXZ]
month:JanFebMarAprMayJunJulAugSepOctNovDec)
symtab:(sym:CLF9CLH9`CLZ9)


I want join symtab with m on 3rd character of symtab.sym=m.code
and return month from m

I am able to get the 3rd character from sym column of symtab table
with the following
{1#x} each {-2#string x} each exec sym from symtab

not sure if it is possible to do this in q


Any help with this is highly appriciated

To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1076)

q)update month:m[(code:$'string[sym]@'2);month]from symtab
sym month

CLF9 Jan
CLH9 Mar
CLZ9 Dec

or

q)delete code from(update code:`$'string[sym]@'2 from symtab)lj m
sym month

CLF9 Jan
CLH9 Mar
CLZ9 Dec

if you would work with a dictionary:
q)d:exec code!month from m
q)update month:d`$'string[sym]@'2 from symtab
sym month

CLF9 Jan
CLH9 Mar
CLZ9 Dec

Regards,
Attila
On 22 Oct 2009, at 15:24, Ramdas wrote:

>
> Hi,
>
> I am new to KDB so excuse me..
>
>
> Can someone help me with the join query as described below..
>
> I have two tables
>
> m:([code:FGHJKMNQUVXZ]
> month:JanFebMarAprMayJunJulAugSepOctNovDec)
> symtab:(sym:CLF9CLH9`CLZ9)
>
>
> I want join symtab with m on 3rd character of symtab.sym=m.code
> and return month from m
>
> I am able to get the 3rd character from sym column of symtab table
> with the following
> {1#x} each {-2#string x} each exec sym from symtab
>
> not sure if it is possible to do this in q
>
>
> Any help with this is highly appriciated
>
> >

On Oct 22, 2009, at 10:24 PM, Ramdas wrote:

> I have two tables
>
> m:([code:FGHJKMNQUVXZ]
> month:JanFebMarAprMayJunJulAugSepOctNovDec)
> symtab:(sym:CLF9CLH9`CLZ9)
>
>
> I want join symtab with m on 3rd character of symtab.sym=m.code
> and return month from m

with your tables, this works:

q)update month:get each m@/:`$'(string sym)@'2 from symtab
sym month

CLF9 Jan
CLH9 Mar
CLZ9 Dec

i’d suggest this tho:

q)m:“FGHJKMNQUVXZ”!JanFebMarAprMayJunJulAugSepOctNovDec
q)update month:m(string sym)@'2 from symtab
sym month

CLF9 Jan
CLH9 Mar
CLZ9 Dec

Thanks guysOn Oct 22, 9:48?am, Aaron Davies <aaron.dav…> wrote:> On Oct 22, 2009, at 10:24 PM, Ramdas wrote:>> > I have two tables>> > m:([code:FGHJKMNQUVXZ]> > month:JanFebMarAprMayJunJulAugSepOctNovDec)> > symtab:(sym:CLF9CLH9CLZ9)&gt;&gt; &gt; I want join symtab with m ?on ? 3rd character of symtab.sym=m.code&gt; &gt; and return month from m&gt;&gt; with your tables, this works:&gt;&gt; q)update month:get each m@/:$'(string sym)@'2 from symtab> sym ?month> ----------> CLF9 Jan> CLH9 Mar> CLZ9 Dec>> i’d suggest this tho:>> q)m:“FGHJKMNQUVXZ”!JanFebMarAprMayJunJulAugSepOctNovDec> q)update month:m(string sym)@'2 from symtab> sym ?month> ----------> CLF9 Jan> CLH9 Mar> CLZ9 Dec</aaron.dav…>

X-Mailer: Apple Mail (2.936)> q)delete code from(update code:$'string[sym]@'2 from symtab)lj mor for a variation,q)delete code from update code.month from update code:m$` $'string[sym]@'2 from symtabfkeys are underused