Hi Gurus,
I have been trying to make this work for God knows how many days, and it simply won’t. Most will probably find this code extremely ugly, but I am new to KDB+/Q so please don’t be too harsh.
The problem is with applying “uj” to a splayed table “t0”.
Will someone very knowledgeable please take a look at this and tell me why on earth this is not working.
This is the result I am after:
Tpx T0D18_51_05_383000001 T0D18_51_05_384000002 T0D18_51_05_494000003 T0D18_51_05_655000004 T0D18_51_05_655000005 T0D18_51_05_725000006
84850 -1
73998 -1
72900 -1
69305 -1
69998 -1
63833 1
And this is table t which I use to construct t0
t
date time qty Tpx
2016.09.30 0D18:51:05.383000001 -1 84850
2016.09.30 0D18:51:05.384000002 -1 73998
2016.09.30 0D18:51:05.494000003 -1 72900
2016.09.30 0D18:51:05.655000004 -1 69305
2016.09.30 0D18:51:05.655000005 -1 69998
2016.09.30 0D18:51:05.725000006 1 63833
2016.09.30 0D18:51:05.725000007 1 63633
2016.09.30 0D18:51:05.726000008 1 63433
2016.09.30 0D18:51:05.727000009 1 63233
2016.09.30 0D18:51:05.727000010 1 63033
Below is my code:
\c 50 150
t:(“DNII”; enlist “,”) 0: `somepath…
d:0;
c:2;
show “generating initial t0 for LOB”
t0: flip (exec ${"T",ssr[x;"[:.]";"_"]}each string time from c#t)!c#(enlist int$());
show “adding Tpx to t0”
update Tpx:int$() from t0
t0: `Tpx xcols t0
show “storing empty t0 on disk”
`:/db/tmp/t0/ set t0
a: desc exec ($"T",/: ssr[; "[:.]"; "_"] each string asc exec distinct time from t[d+til c])#(($“T”,/: ssr[; “[:.]”; “_”] each string time)!qty) by Tpx:Tpx from t[d+til c];
a:0!a;
show “storing on disk”;
{:/db/tmp/t0/ upsert .Q.en[:/db;] t0 uj value x} each `a;
pos:c; / pos starts at c. ALWAYS!
while[d<=4;
n:count get `:/db/tmp/t0/Tpx;
temp1: $"T", ssr[string t[pos;time]; “[:.]”;“_”];
@[:/db/tmp/t0;temp1;:;n#(int$())];
@[:/db/tmp/t0;.d;,;temp1];
pos+:1;
temp2: $"T", ssr[string t[pos;time]; “[:.]”;“_”];
@[:/db/tmp/t0;temp2;:;n#(int$())];
@[:/db/tmp/t0;.d;,;temp2];
pos+:1;
d+:c;;
a: desc exec ($"T",/: ssr[; "[:.]"; "_"] each string asc exec distinct time from t[d+til c])#(($“T”,/: ssr[; “[:.]”; “_”] each string time)!qty) by Tpx:Tpx from t[d+til c];
a:0!a;
show “storing on disk”;
{:/db/tmp/t0/ upsert .Q.en[:/db;] t0 uj value x} each `a; / THIS IS WHERE THE PROBLEM IS
] / end of 1st while loop;
I can:
-
create an empty t0 table,
-
add Tpx col to it and move the col to the beginning of t0,
-
use “uj” to merge t0 with a,
-
add 2 empty int type cols to t0
-
then use “uj” again to merge with a new “a” table
After step 5, t0 looks like this
Tpx T0D18_51_05_383000001 T0D18_51_05_384000002 T0D18_51_05_494000003 T0D18_51_05_655000004
84850 -1
73998 -1
72900 -1
69305 -1
-
I can add 2 more int type cols to t0 on the disk
-
But then I cannot, whatever I do, merge t0 (from step 5) to a new “a” table… I have tried everything. To add to my frustration, I can
tt: get `:/db/tmp/t0
tt
Tpx T0D18_51_05_383000001 T0D18_51_05_384000002 T0D18_51_05_494000003 T0D18_51_05_655000004 T0D18_51_05_655000005 T0D18_51_05_725000006
84850 -1
73998 -1
72900 -1
69305 -1
a
Tpx T0D18_51_05_655000005 T0D18_51_05_725000006
69998 -1
63833 1
Then
tt uj a <- this works fine when tt is in the memory… but when its splayed this doesn’t work.
Please help, your help is very much appreciated as always.
Regards,
VA.