I understand this title might be confusing.the problem is actually very simple, you do a queue:jj: select x by exchange, date from rawdatajj should look like:exchange date | xexchange1 date1 | x1exchange1 date2 | x2…exchange2 date1 | x4…Question: how can I turn this keyed table into a normal rectangle table like date1 date2 date3 …exchange1 x1 x2 x3exchange2 x4 x5 x6…Can any help? cannot figure out an elegant solution hereThanks a lot!-- CHEN, Cheng
So you want the table column names to be dates? You’ll have to castthem into symbol type.This implementation assumes each exchange has the same set of dates asthe other exchangesq)tbl:( a:raze 5#'aabbcc; b: raze flip 3#'date$til 5; c: 15 ?100.0)q)jj:select b,c by a from tblq)jja | bc–|---------------------------------------------------------------------------------------------------aa| 2000.01.01 2000.01.02 2000.01.03 2000.01.04 2000.01.05 75.0449130.85131 44.10662 54.73385 59.3737bb| 2000.01.01 2000.01.02 2000.01.03 2000.01.04 2000.01.05 8.84071817.88229 76.32325 92.7445 12.47049cc| 2000.01.01 2000.01.02 2000.01.03 2000.01.04 2000.01.05 36.6148570.19831 72.69904 47.99172 98.75418q)raze {flip (exchage,$string jj[b]) ! (enlist x),enlist eachjj[x][c]} each (key jj)`aexchage 2000.01.01 2000.01.02 2000.01.03 2000.01.04 2000.01.05--------------------------------------------------------------aa 75.04491 30.85131 44.10662 54.73385 59.3737bb 8.840718 17.88229 76.32325 92.7445 12.47049cc 36.61485 70.19831 72.69904 47.99172 98.75418Does that help?On Aug 12, 2:50?pm, “CHEN, Cheng” <chench…> wrote:> I understand this title might be confusing.>> the problem is actually very simple, you do a queue:>> jj: select x by exchange, date from rawdata>> jj should look like:> exchange ? ? ? ? ?date ? ? ? | ? ? ? ?x> exchange1 ? ? ? date1 ? ? | ? ? ? ? x1> exchange1 ? ? ? date2 ? ? | ? ? ? ? x2> …> …> exchange2 ? ? ? date1 ? ? | ? ? ?x4> …>> Question: how can I turn this keyed table into a normal rectangle table like>> ? ? ? ? ? ? ? ? ? ? ? ? ? ? date1 ? date2 ?date3 ?..> exchange1 ? ? ? ? ?x1 ? ? ? ? x2 ? ? ? ? x3> exchange2 ? ? ? ? ?x4 ? ? ? ? x5 ? ? ? ? x6> …>> Can any help? cannot figure out an elegant solution here>> Thanks ?a lot!> –> CHEN, Cheng</chench…>
pivot.
given something like this
q)t:(ex:abcabcab`c;date:.z.D+9#til 5;v:til 9)
then
q)exchanges:exec distinct ex from t
q)0^exec exchanges!(ex!v)exchanges by date:date from t
| date | a b c |
|---|---|
| 2010.08.13 | 0 0 5 |
| 2010.08.14 | 6 1 0 |
| 2010.08.15 | 0 7 2 |
| 2010.08.16 | 3 0 8 |
| 2010.08.17 | 0 4 0 |
sorry, you wanted dates as col names, so
q)0^exec (`$string dates)!(date!v)dates by ex:ex from t
ex| 2010.08.13 2010.08.14 2010.08.15 2010.08.16 2010.08.17
–| ------------------------------------------------------
a | 0 6 0 3 0
b | 0 1 7 0 4
c | 5 0 2 8 0