cancel
Showing results for 
Search instead for 
Did you mean: 

table query

Hungry_Goat
New Contributor
t1:([] ts: `time$(); sym: `$())
insert[`t1;(08:30:00.422;`GOOG)]
insert[`t1;(08:30:01.522;`GOOG)]
insert[`t1;(08:30:03.702;`GOOG)]

b: select ts: (string[`second$ts],":",string[`int$mod[ts;1000]]) from t1
q)b
ts        
----------
"08:30:00"
"08:30:01"
"08:30:03"
":"       
"422"     
"522"     
"702"     

Expected
--------------
ts        
----------
"08:30:00:422"
"08:30:01:522"
"08:30:03:702"

What is going on? This should be such a simple construct. Why do I have to spend 2 hours on this?
5 REPLIES 5

rory_ororke
New Contributor
q)t1:([] ts: `time$(); sym: `$())
q)insert[`t1;(08:30:00.422;`GOOG)]
,0
q)insert[`t1;(08:30:01.522;`GOOG)]
,1
q)insert[`t1;(08:30:03.702;`GOOG)]
,2
q)t1
ts           sym
-----------------
08:30:00.422 GOOG
08:30:01.522 GOOG
08:30:03.702 GOOG
q)select ts: (string[`second$ts],'":",'string[`int$mod[ts;1000]]) from t1
ts
--------------
"08:30:00:422"
"08:30:01:522"
"08:30:03:702"



Thanks Rory

TerryLynch
New Contributor II
You'd be better off string-search-replacing than trying to concatenate.

q)select ssr'[;".";":"]string ts from t1
ts
--------------
"08:30:00:422"
"08:30:01:522"
"08:30:03:702"
q)

A slightly quicker solution (given that the position of the dot is fixed) would be

q)select .[;(::;8);:;":"]string ts from t1
ts
--------------
"08:30:00:422"
"08:30:01:522"
"08:30:03:702"


Terry

Thanks Terry
Can you please elaborate on the syntax 
select ssr'[;".";":"]string ts from t1

Why do I need to use ' (each-both) ?
I am assuming ssr is applied to each row and not after all the rows are returned.

E.g.
q)a:08:30:00.422
q)ssr[string a;".";":"]
"08:30:00:422"

q)b:(08:30:00.422 09:10:00.123)
q)ssr'[string b;".";":"]
"08:30:00:422"
"09:10:00:123"

Thanks

Correct, ssr has to be applied to each string (which is a list of lists).

To clarify - ssr is the slowest solution but the easiest to read and most succinct. If speed is a concern you should use the dot-indexing approach.

In my syntax below i deliberately projected the ssr function (left the first param blank) to force the column name to be parsed correctly. It could also have been written

select ts:ssr'[string ts;".";":"] from t1

Or

select ts:{ssr[x;".";":"]}each string ts from t1

Etc. Was just trying to shave some characters!

Terry

In the case that the dot is not fixed - the following solution from Igor is ~10x quicker that ssr'.

q)show t1:([]ts:100000?08:30:00.0)
ts
------------
03:37:12.823
00:53:18.265
05:50:34.147
..

q)c2p:{p:ss[r:raze x;"."];r[p]:":";(0,-1_sums count'[x])_r}    / http://stackoverflow.com/a/38916772/2039505

q)\t a:select ts:ssr'[string ts;".";":"] from t1
404
q)\t b:select c2p string ts from t1
43
q)a~b
1b


Connor