0

I am trying to sort 1 or 2 columns in a hdb in kdb but failed. This is the code I have

fncol[dbdir;`trade;`sym;xasc];

and got a length error when I called it. But I don't have a length error if I use this code

fncol[dbdir;`trade;`sym;asc];.

However this only sorts the sym column itself. I want the data from other columns change according to sym column as well.

In addition, I would like to apply parted attribute to sym column. Also, I tried to sort this way

fncol[dbdir;`trade;`sym`ptime;xasc];. also failed
Cathal O'Neill
  • 2,522
  • 1
  • 6
  • 17
Terry
  • 523
  • 9
  • 21

2 Answers2

2

You should always be careful with dbmaint.q if you are unsure what it is going to do. I gather from the fact asc worked after xasc that you are using a test hdb each time.

fncol should be used with unary functions i.e. 1 argument. It's use case is for modifying individual columns. What you are trying to do is modifying the entire table as you want to sort the entire table relative to the sym column. Using .Q.dpft for each date is what you want as outlined by Cathal in your follow-up question. using .Q.dpft function to resave table

When you run this fncol[dbdir;`trade;`sym;xasc]; You are saving down a projection in place of the sym column in each date.

fncol[`:.;`trades;`sym;xasc];
select from trades where date = 2014.04.21
'length
  [0]  select from trades where date = 2014.04.21

q)get `:2014.04.21/trades/sym
k){$[$[#x;~`s=-2!(0!.Q.v y)x;0];.Q.ft[@[;*x;`s#]].Q.ord[<:;x]y;y]}[`p#`sym$`A..
// This is the k definition of xasc with the sym column as the first parameter.

q)xasc
k){$[$[#x;~`s=-2!(0!.Q.v y)x;0];.Q.ft[@[;*x;`s#]].Q.ord[<:;x]y;y]}

// Had you needed to fix your hdb, I managed to undo this using value and indexing to the sym col data.

fncol[`:.;`trades;`sym;{(value x)[1]}];

q)select from trades where date = 2014.04.21
date       sym  time                          src price size
------------------------------------------------------------
2014.04.21 AAPL 2014.04.21D08:00:12.155000000 N   25.31 2450
2014.04.21 AAPL 2014.04.21D08:00:42.186000000 N   25.32 289
2014.04.21 AAPL 2014.04.21D08:00:51.764000000 O   25.34 3167

asc will not break the hdb as it just takes 1 argument and saves down ONLY the sym column in ascending order not the table.

Matt Moore
  • 2,705
  • 6
  • 13
0

Is there any indication of what date is failing with a length error? It could be something wrong with one of the partitions.

Perhaps if you try to load one of the dates into memory and sort it manually IE

`sym xasc select from trade where date=last date

that might indicate if there's a specific partition causing issues.

FYI if you're intersted in applying the p# attribute you should try setattrcol in dbmaint.q. I think the data will need to be sorted first though.

Cathal O'Neill
  • 2,522
  • 1
  • 6
  • 17
  • yes but i got `s eorr or something. i think i need to sort first – Terry Nov 27 '20 at 19:44
  • i got the length error only when i call the table. not when sorting. – Terry Nov 27 '20 at 19:45
  • You're getting a length error when you try to load the database? Or when you try to select from it? – Cathal O'Neill Nov 27 '20 at 19:45
  • when calling the table. loading is fine – Terry Nov 27 '20 at 19:51
  • Can you check if the size of the sym file within one of the partition directories is the same size as another integer column? They should be the same. – Cathal O'Neill Nov 27 '20 at 19:56
  • ok i think i'll load and resave. do you know why i got `u error when i apply parted attribute to hdb sym column? i used dbmaint setattricol function – Terry Nov 27 '20 at 21:07
  • The sym column needs to be sorted first Terry before the attribute can be applied, that's why you were seeing the `'u-fail` error. Are you able to sort the data before saving it down? – Cathal O'Neill Nov 27 '20 at 21:09