[cillianreilly]

You can also forgo any conditional statements in this case:

 

q)update r:p1*p2*not p1=neg p2 from t1 
TimeStamp stock p1 p2 r 
------------------------ 
2018.01.01 AAPL 1 -1 0 
2018.01.01 AAPL 2 1 2 
2018.01.02 GOOGL -2 2 0 
2018.01.02 GOOGL 3 0 0

 

 

 

[cillianreilly]

Assuming the sym column in your original table is distinct:

q)update upperTime:1 from t where any each price<=level 
sym price level lowerTime upperTime 
------------------------------------------ 
abc 10 20.3 13.5 15 1 
cde 11 21.3 13.5 16 1

[cillianreilly]

The exec statement creates a list of lists – enclosing these in square brackets means kdb+ treats this as a singular item passed to your function.  In order to treat the list as the first and second arguments respectively, you need apply:

q)tab:([]col1:`a`b`c;col2:1 2 3) q).my.func:{0N!(x;y;z);} q){.my.func[x;y;`field]}./:flip value exec col1,col2 from tab; (`a;1;`field) (`b;2;`field) (`c;3;`field)

 

In this case though, you can keep the entire operation contained in the qsql statement. kdb+ will extend the atomic symbol to match the length of the table columns:

q)exec .my.func'[col1;col2;`field]from tab; (`a;1;`field) (`b;2;`field) (`c;3;`field)

 

 

[cillianreilly]

For operations on partitioned tables, please refer to dbmaint.q. In particular, the rename column should achieve what you’re looking for.

https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.md#renamecol

[cillianreilly]

First, remember that a table itself is just a list of dictionaries.

q)0N!/:tab; 
`col1`col2!(`a;1) 
`col1`col2!(`b;2) 
`col1`col2!(`c;3)

So, when you tabulate a table, each entry in the original table (a dictionary) just becomes an entry in the new table column:

q)([]tab) 
tab 
----------------- 
`col1`col2!(`a;1) 
`col1`col2!(`b;2) 
`col1`col2!(`c;3) 

q)type ([]tab) 
98h 

q)type each tab 
99 99 99h 

q)type each([]tab) 
99 99 99h

 

[cillianreilly]

You haven’t added the rk column to the 2023.06.01 partition.

 

q){x!`rk in/:get each` sv/:(hsym`$string x),:`trade`.d}date 
2023.06.01| 0 
2023.06.02| 0 
2023.06.05| 1 
2023.06.06| 1

 

You can reference dbmaint.q – it contains utility functions for working on partitioned databases. For this case, the fixtable function would help you here – adding the rk column into the partitions that don’t currently contain it.

In general, better practice is to add the empty column to all partitions first (addcol), and then write down the partitions containing data. This keeps the HDB in a good state throughout the operation.

[cillianreilly]

The lambda isn’t necessary here:

 

q)update m:price@'n from tab 
time        | price n m 
------------| --------------------------------- 
10:03:54.347| 20.83 21.44 26.83 29.83 0 20.83e 
10:04:05.827| 88.88 88.75 83.27 823.77 1 88.75

 

Not useful for the general case, but here you can forgo the explicit n column and use the virtual index column instead:

 

q)tab:([time:10:03:54.347 10:04:05.827]price:(20.83 21.44 26.83 29.83e;88.88 88.75 83.27 823.77f)) 
q)update m:price@'i from tab 
time        | price m 
------------| ------------------------------- 
10:03:54.347| 20.83 21.44 26.83 29.83 20.83e 
10:04:05.827| 88.88 88.75 83.27 823.77 88.75

 

[cillianreilly]

David has already answered the question here, but it’s worth noting that you can do this operation very succinctly using .Q.dd:

q)update symsrc:.Q.dd'[sym;src]from trades 

time                          sym src price size symsrc 
-------------------------------------------------------- 
2023.06.04D10:22:20.088377000 ORCL L 40.67 3984 ORCL.L

 

[cillianreilly]

I think this is due to the constraints specified here: https://code.kx.com/q/ref/amend/#on-disk, in particular that only vectors of types 1-19 can be updated directly on disk. Given that weirdPartition has type 0, it can’t be updated on disk, but has no such constraints in memory.

Note: Personally unclear on whether the wording on the above link (“Certain vectors (types 1-19) can be updated directly on disk without the need to fully rewrite the file. “) means it can’t be done, or it can be done but that the entire file must be rewritten, and there is another issue at play here. I’m leaning towards the former, and that modifications must be done in memory first before writing.

[cillianreilly]

It’s faster to keep track of the running products at each step:

tree:([]parent:`A`A`A`B`B`E`E;child:`B`C`D`E`F`G`H;data:1 2 3 4 5 6 7); 
sort:{x iasc 2#/:x:x@'(-1+count each x),:1 0} 
step:{.[z;(::;0);*;]y -2#/:z:(z,'x l)where(l:last each z)in key x} 
walk2:{ d:exec child!parent from x; w:exec(child,'parent)!data from x; sort raze 1_(step[d;w;])1,'(except/)x`child`parent } walk2 tree `A `C 2 `A `D 3 `A `F 5 `A `G 24 `A `H 28 `B `F 5 `B `G 24 `B `H 28 `E `G 6 `E `H 7 
q)\ts do[50000;walk tree] 
1930 3584j 
q)\ts do[50000;walk2 tree] 
1259 3440j

[cillianreilly]

Another method:

tree:([]parent:`A`A`A`B`B`E`E;child:`B`C`D`E`F`G`H;data:1 2 3 4 5 6 7); 
dl:-1_ 
s:{x iasc 2#/:x} // sort 
lr:{flip dl(x)y} // leaf to root 
lar:{(sum[n]-2)dlx where n:not null x} // leaf to all roots 
gw:{((last;first)@:y),prd x dl flip 1 nexty} // get weights 
walk:{ d:exec child!parent from x; w:exec(child,'parent)!data from x; s gw[w;]each raze lar each lr[d;](except/)x`child`parent } walk tree `A `C 2 `A `D 3 `A `F 5 `A `G 24 `A `H 28 `B `F 5 `B `G 24 `B `H 28 `E `G 6 `E `H 7

[cillianreilly]

Sounds like this link is what you’re after: https://code.kx.com/q/interfaces/q-client-for-bloomberg/

[cillianreilly]

q)progression:"C#D#EF#G#A#B" 
q)notes:flip progression mod[;12]til[13]+/:progression? 
q)notes
"EADGBE" 
"EADGBE" 
"F###CF" 
"#BEA##" 
"GCF#DG" 
"###B##" 
"ADGCEA" 
"####F#" 
"BEAD#B" 
"CF##GC" 
"##BE##" 
"DGCFAD" 
"######" 
"EADGBE"

First row is the open strings, and runs as far as the 12th fret, at which point the pattern repeats. Also works for alternative tunings:

q)notes”DADGBE” // Drop D

q)notes”DADGAD” // DADGAD

[cillianreilly]

Always room for improvement. There is no need to pass the number of open lambdas between each iteration when that can be easily checked during each iteration. Now there is no need to pass a 2 item list, we can just converge on the input string itself. The true condition in the above version is also a bit suspect, this is cleaner.

paste:{value{$[(“”~r:read0 0)and not sum 124-7h$x inter”{}”;x;x,` sv enlist r]}/[“”]}

@SJT, happy to write this up for Awesome Q.

[cillianreilly]

I’m not sure what your complete data set looks like – but using a best guess, I get an expected result.

q)tt:([]DT:`timestamp$2012.12.31 2013.01.02 2013.01.03 2013.01.04 2013.01.07 2013.01.08 2013.01.09 2013.01.10 2013.01.11 2013.01.14;O:1405.22 1426.19 1462.42 1459.37 1466.47 1461.89 1457.15 1461.02 1472.12 1472.05;C:1425.69 1461.36 1459.07 1466.1 1461.77 1457.05 1461.04 1471.99 1471.71 1470.79) 
q)select first O,last C by DT:(2 xbar DT.date)+1D16:00 from tt 
DT                           | O       C 
-----------------------------| --------------- 
2013.01.01D16:00:00.000000000| 1405.22 1425.69 // open 31st, close 31st 
2013.01.03D16:00:00.000000000| 1426.19 1459.07 // open 2nd, close 3rd 
2013.01.05D16:00:00.000000000| 1459.37 1466.1 // open 4th, close 4th 
2013.01.07D16:00:00.000000000| 1466.47 1461.77 // open 7th, close 7th 
2013.01.09D16:00:00.000000000| 1461.89 1461.04 // open 8th, close 9th 
2013.01.11D16:00:00.000000000| 1461.02 1471.71 // open 10th, close 11th 
2013.01.15D16:00:00.000000000| 1472.05 1470.79 // open 14th, close 14th

I also don’t see how you can have 2012.12.31D16:00:00 in both results (1 day/2 day buckets), as it shouldn’t appear in the 2-day bucket query (unless you have some other row dated 2012.12.30 in your table you’re not showing us?)

q)(2 xbar 2012.12.31)+0D16:00 1D16:00 
2012.12.31D16:00:00.000000000 2013.01.01D16:00:00.000000000

This looks like some data quality issue somewhere rather than issue with xbar – if you can provide a small stand alone sample of your data that replicates the problem, I can take a look using that.