Hello,
I have this query
select cond, tc1:{$(ssr[x;" ";""])(0)} each cond, tc2:{$(ssr[x;" “;”“])(1)} each cond, tc3:{`$(ssr[x;” “;”"])(2)} each cond from trade
but its proving to be fairly slow. Wondering if anybody has any ideas on how to speed it up? Wondering if its possible to combine all the each iterations into one?
cond is a string and I want to pull the first, second and third letters out into their own columns.
q)t:(cond:(“1 2 3”;“a b c”;“x y z”))<o:p></o:p>
q)t,'flip tc1tc2tc3!("SSS";" ") 0:tcond<o:p></o:p>
cond tc1 tc2 tc3<o:p></o:p>
-------------------<o:p></o:p>
“1 2 3” 1 2 3<o:p></o:p>
“a b c” a b c<o:p></o:p>
“x y z” x y z<o:p></o:p>
<o:p> </o:p>
From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Roni Hoffman
Sent: Friday, July 3, 2015 11:32 AM
To: personal-kdbplus@googlegroups.com
Subject: [personal kdb+] Multiple Each combined to one<o:p></o:p>
<o:p> </o:p>
Hello,<o:p></o:p>
<o:p> </o:p>
I have this query<o:p></o:p>
<o:p> </o:p>
select cond, tc1:{$(ssr[x;" ";""])(0)} each cond, tc2:{$(ssr[x;" “;”“])(1)} each cond, tc3:{`$(ssr[x;” “;”"])(2)} each cond from trade<o:p></o:p>
<o:p> </o:p>
but its proving to be fairly slow. Wondering if anybody has any ideas on how to speed it up? Wondering if its possible to combine all the each iterations into one?<o:p></o:p>
<o:p> </o:p>
cond is a string and I want to pull the first, second and third letters out into their own columns.<o:p></o:p>
–
Submitted via Google Groups
Oh, first second third letters is:<o:p></o:p>
<o:p> </o:p>
q)update tc1:$string cond[;0], tc2:$string cond[;1], tc3:`$string cond[;2] from t<o:p></o:p>
cond tc1 tc2 tc3<o:p></o:p>
-----------------<o:p></o:p>
“123” 1 2 3<o:p></o:p>
“abc” a b c<o:p></o:p>
“xyz” x y z<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Not sure why you need the ssr but you can do this once if you need:<o:p></o:p>
<o:p> </o:p>
update tc1:$string cond[;0], tc2:$string cond[;1], tc3:`$string cond[;2] from update ssr[;" “;”"]each cond from t<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of David Demner (AquaQ)
Sent: Friday, July 3, 2015 11:43 AM
To: personal-kdbplus@googlegroups.com
Subject: RE: [personal kdb+] Multiple Each combined to one<o:p></o:p>
<o:p> </o:p>
q)t:(cond:(“1 2 3”;“a b c”;“x y z”))<o:p></o:p>
q)t,'flip tc1tc2tc3!("SSS";" ") 0:tcond<o:p></o:p>
cond tc1 tc2 tc3<o:p></o:p>
-------------------<o:p></o:p>
“1 2 3” 1 2 3<o:p></o:p>
“a b c” a b c<o:p></o:p>
“x y z” x y z<o:p></o:p>
<o:p> </o:p>
From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Roni Hoffman
Sent: Friday, July 3, 2015 11:32 AM
To: personal-kdbplus@googlegroups.com
Subject: [personal kdb+] Multiple Each combined to one<o:p></o:p>
<o:p> </o:p>
Hello,<o:p></o:p>
<o:p> </o:p>
I have this query<o:p></o:p>
<o:p> </o:p>
select cond, tc1:{$(ssr[x;" ";""])(0)} each cond, tc2:{$(ssr[x;" “;”“])(1)} each cond, tc3:{`$(ssr[x;” “;”"])(2)} each cond from trade<o:p></o:p>
<o:p> </o:p>
but its proving to be fairly slow. Wondering if anybody has any ideas on how to speed it up? Wondering if its possible to combine all the each iterations into one?<o:p></o:p>
<o:p> </o:p>
cond is a string and I want to pull the first, second and third letters out into their own columns.<o:p></o:p>
–
Submitted via Google Groups
Thanks David! thats really close to what I need, except someitmes the cond string has characters together
so
q)t:(cond:(“12 3”;“ab c”;“x y z”))
q)t,'flip tc1tc2tc3!("SSS";" ") 0:tcond
results in
cond tc1 tc2 tc3
“12 3” 12 3
“ab c” ab c
“xy z” xy z
That’s why I was trying to use ssr to get rid of all white spaces first
Perfect, yea that works, Thanks!
Hi,
If you want to keep the source column as is, and an alternative method…
//create test data with 1m rows
q)n:3000000;n2:500000;
q)t:(cond:d[n2?n2],1 rotate/:(d:" "sv/:3 cut n?string .Q.a) n2?n2);
//use exec to create dict, then flip and join onto orig tab
q)\ts t,'exec flip tc1tc2tc3!flip $string cond @'where each not cond=" " from t
593 168389584
q)\ts update tc1:$string cond[;0], tc2:$string cond[;1], tc3:`$string cond[;2] from update ssr[;" “;”"]each cond from t
3118 65827264
//check tabs match with exception of cond
q)(delete cond from t,'exec flip tc1tc2tc3!flip $string cond @'where each not cond=" " from t)~delete cond from update tc1:$string cond[;0], tc2:$string cond[;1], tc3:`$string cond[;2] from update ssr[;" “;”"]each cond from t
1b
HTH,
Sean
Thanks Sean yes that’s actually a better solution. I can get it to work with your example but when I try it with my table I am getting a length error. I think because my cond does not always have 4 characters. Is there a way to change that exec to have empty cols as well?
cond
"T "
" FT "
"T "
" TI"
Hi,
So I have copied your example… you have 5,4,5,4 chars, with a mix of nulls in each cond.
t:(cond:("T “;” FT ";“T “;” TI”))
You can use my example along with padding…3$..to ensure after the removal of whitespace you have 3 chars
t,'exec flip tc1tc2tc3!flip $string 3$ cond @'where each not cond=" " from t
Let us know if this is or is not what you want.
Cheers,
Sean
Thanks very much Sean, This seems to be exactly what I need. I wont know until tomorrow now, but looks good!<o:p></o:p>
<o:p> </o:p>
From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Sean O’Hagan
Sent: July 6, 2015 6:57 PM
To: personal-kdbplus@googlegroups.com
Subject: [personal kdb+] Re: Multiple Each combined to one<o:p></o:p>
<o:p> </o:p>
Hi,<o:p></o:p>
<o:p> </o:p>
So I have copied your example… you have 5,4,5,4 chars, with a mix of nulls in each cond. <o:p></o:p>
t:(cond:("T “;” FT ";“T “;” TI”))<o:p></o:p>
<o:p> </o:p>
You can use my example along with padding…3$..to ensure after the removal of whitespace you have 3 chars<o:p></o:p>
<o:p> </o:p>
t,'exec flip tc1tc2tc3!flip $string 3$ cond @'where each not cond=" " from t<o:p></o:p>
<o:p> </o:p>
Let us know if this is or is not what you want.<o:p></o:p>
<o:p> </o:p>
Cheers,<o:p></o:p>
Sean<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>