cancel
Showing results for 
Search instead for 
Did you mean: 

Help with join

Ramdas
New Contributor
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:`F`G`H`J`K`M`N`Q`U`V`X`Z]
month:`Jan`Feb`Mar`Apr`May`Jun`Jul`Aug`Sep`Oct`Nov`Dec)
symtab:([]sym:`CLF9`CLH9`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

4 REPLIES 4

Attila
New Contributor

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:`F`G`H`J`K`M`N`Q`U`V`X`Z]
> month:`Jan`Feb`Mar`Apr`May`Jun`Jul`Aug`Sep`Oct`Nov`Dec)
> symtab:([]sym:`CLF9`CLH9`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
>
> >


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

Aaron_Davies
New Contributor


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

> I have two tables
>
> m:([code:`F`G`H`J`K`M`N`Q`U`V`X`Z]
> month:`Jan`Feb`Mar`Apr`May`Jun`Jul`Aug`Sep`Oct`Nov`Dec)
> symtab:([]sym:`CLF9`CLH9`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"!`Jan`Feb`Mar`Apr`May`Jun`Jul`Aug`Sep`Oct`Nov`Dec
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 wrote:> On Oct 22, 2009, at 10:24 PM, Ramdas wrote:>> > I have two tables>> > m:([code:`F`G`H`J`K`M`N`Q`U`V`X`Z]> > month:`Jan`Feb`Mar`Apr`May`Jun`Jul`Aug`Sep`Oct`Nov`Dec)> > symtab:([]sym:`CLF9`CLH9`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"!`Jan`Feb`Mar`Apr`May`Jun`Jul`Aug`Sep`Oct`Nov`Dec> q)update month:m(string sym)@'2 from symtab> sym �month> ----------> CLF9 Jan> CLH9 Mar> CLZ9 Dec