How could I clean up this query?

Community Support
1

tab:(time:“p”$til 6; sym:6#AB; price:0.1*til 6);

ans1:(sym:AB; price0:0 0.1; price1:0.2 0.3; price2:0.4 0.5);

ans2:(time:“p”$0 1; sym:AB; price0:0 0.1; price1:0.2 0.3; price2:0.4 0.5);

timesym xcols

(0!

select first time by sym from tab)

^

(0!

select 

price0:first @[;0] each price, 

price1:first @[;1] each price, 

price2:first @[;2] each price 

by sym from

select price by sym from tab

)

2

Hi Yan Yan,

How about this?

q)ans3:timesym xcols 0!select time: first time, price0:price[0], price1:price[1], price2:price[2] by sym from tab

q)ans3

time sym price0 price1 price2

------------------------------------------------------

2000.01.01D00:00:00.000000000 A 0 0.2 0.4

2000.01.01D00:00:00.000000001 B 0.1 0.3 0.5

q)ans2 ~ ans3

1b

Regards,

Paul

3

use ‘exec … by’

q)timesym xcols 0!exec timeprice0price1price2!(first[time],price 0 1 2) by sym:sym from tab
time                          sym price0 price1 price2

2000.01.01D00:00:00.000000000 A   0      0.2    0.4  
2000.01.01D00:00:00.000000001 B   0.1    0.3    0.5  

4

ty

5

Nick’s solution looks like .stat.ohlc and .stat.summary in his book.

.stat.ohlc   ohlc:{ o h l c!( first; max; min; last)@: x}

Psaris, Nick (2015-03-19). Q Tips: Fast, Scalable and Maintainable Kdb+ (Kindle Locations 4589-4593). Vector Sigma. Kindle Edition. 

.stat.summary   summary:{ n mn mx md ` dv!( count; min; max; med; sdev)@: x}

Psaris, Nick (2015-03-19). Q Tips: Fast, Scalable and Maintainable Kdb+ (Kindle Locations 4607-4611). Vector Sigma. Kindle Edition.