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:
1. create an empty t0 table,
2. add Tpx col to it and move the col to the beginning of t0,
3. use "uj" to merge t0 with a,
4. add 2 empty int type cols to t0
5. 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
6. I can add 2 more int type cols to t0 on the disk
7. 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
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.