grokpronoia
Reputation: 123
kdb - How can I recursively update rows in a table?
I have a table of tick data
q)t:([]symbol:8#`ABC;receiverIMID:`rep1`rep1`rep2`rep2`rep1`rep1`rep2`rep2;ID:`2019`2018`2019`2018`2019`2018`2019`2018;priorID:`````2018`2017`2018`2017;date:8#.z.Z;priorDate:(0Nz;0Nz;0Nz;0Nz;.z.Z;.z.Z-1;.z.Z;.z.Z-1);status:8#1)
q)t
symbol receiverIMID ID priorID date priorDate status
---------------------------------------------------------------------------------------
ABC rep1 2019 2020.06.30T18:33:18.039 1
ABC rep1 2018 2020.06.30T18:33:18.039 1
ABC rep2 2019 2020.06.30T18:33:18.039 1
ABC rep2 2018 2020.06.30T18:33:18.039 1
ABC rep1 2019 2018 2020.06.30T18:33:18.039 2020.06.30T18:33:18.039 1
ABC rep1 2018 2017 2020.06.30T18:33:18.039 2020.06.29T18:33:18.039 1
ABC rep2 2019 2018 2020.06.30T18:33:18.039 2020.06.30T18:33:18.039 1
ABC rep2 2018 2017 2020.06.30T18:33:18.039 2020.06.29T18:33:18.039 1
and a table of historic data
q)t2:([]SYM:2#`ABC;IMID:`rep1`rep2;ID:2#`2017;DATE:2#.z.Z-1;PS:15)
q)t2
SYM IMID ID DATE PS
----------------------------------------
ABC rep1 2017 2020.06.29T18:36:48.905 15
ABC rep2 2017 2020.06.29T18:36:48.905 15
I update the status of table t with the PS from table t2 if the SYM, IMID, ID match the symbol, recieverIMID, ID (or) priorID from table t...
tt:select OID:{$[null x;y;x]}'[priorID;ID],symbol, receiverIMID, status from t
th:select ID,SYM,IMID,PS from t2
update status:(exec status from (tt lj 3!cols[tt] xcol th)) from `t
which makes the following updates to table t
symbol receiverIMID ID priorID date priorDate status
--------------------------------------------------------------------------------------
ABC rep1 2019 2020.06.30T00:00:00.000 1
ABC rep1 2018 2020.06.30T00:00:00.000 1
ABC rep2 2019 2020.06.30T00:00:00.000 1
ABC rep2 2018 2020.06.30T00:00:00.000 1
ABC rep1 2019 2018 2020.06.30T00:00:00.000 2020.06.30T00:00:00.000 1
ABC rep1 2018 2017 2020.06.30T00:00:00.000 2020.06.29T00:00:00.000 15
ABC rep2 2019 2018 2020.06.30T00:00:00.000 2020.06.30T00:00:00.000 1
ABC rep2 2018 2017 2020.06.30T00:00:00.000 2020.06.29T00:00:00.000 15
Then I'd like to exec the ID of the row that was just updated, update all rows from table t with that ID, and check if that ID is in priorID, e.g., after updating where priorID in 2017 then update the other rows where ID in 2018, and check if 2018 is in priorID. It would look like the following...
symbol receiverIMID ID priorID date priorDate status
--------------------------------------------------------------------------------------
ABC rep1 2019 2020.06.30T00:00:00.000 1
ABC rep1 2018 2020.06.30T00:00:00.000 15
ABC rep2 2019 2020.06.30T00:00:00.000 1
ABC rep2 2018 2020.06.30T00:00:00.000 15
ABC rep1 2019 2018 2020.06.30T00:00:00.000 2020.06.30T00:00:00.000 1
ABC rep1 2018 2017 2020.06.30T00:00:00.000 2020.06.29T00:00:00.000 15
ABC rep2 2019 2018 2020.06.30T00:00:00.000 2020.06.30T00:00:00.000 1
ABC rep2 2018 2017 2020.06.30T00:00:00.000 2020.06.29T00:00:00.000 15
I'll repeat this process, updating all rows where ID is present and checking to see if ID is in priorID. In this case 2019 is not in priorID so we'll exit the function. Final update table would look like the following...
symbol receiverIMID ID priorID date priorDate status
--------------------------------------------------------------------------------------
ABC rep1 2019 2020.06.30T00:00:00.000 15
ABC rep1 2018 2020.06.30T00:00:00.000 15
ABC rep2 2019 2020.06.30T00:00:00.000 15
ABC rep2 2018 2020.06.30T00:00:00.000 15
ABC rep1 2019 2018 2020.06.30T00:00:00.000 2020.06.30T00:00:00.000 15
ABC rep1 2018 2017 2020.06.30T00:00:00.000 2020.06.29T00:00:00.000 15
ABC rep2 2019 2018 2020.06.30T00:00:00.000 2020.06.30T00:00:00.000 15
ABC rep2 2018 2017 2020.06.30T00:00:00.000 2020.06.29T00:00:00.000 15
I'm assuming we would want to use over (/) for the recursion, though I'm not sure how to properly implement the recursive lookup and updates. Thank you in advance for the help.
Upvotes: 1
Views: 379
Answers (2)
Elliot Tennison
Reputation: 91
The below function will recursively edit the rows of the table as required:
func:{
tt:select OID:{$[null x;y;x]}'[priorID;ID],symbol, receiverIMID, status from t;
$[x[0]=0;
th:select ID,SYM,IMID,PS from t2;
th: select ID,symbol,receiverIMID,status from t where ID in x[2]
];
changedrowpriorids: exec OID from (tt ij 3!cols[tt] xcol th);
changedrowids: exec ID from t where priorID in changedrowpriorids;
update status:(exec status from (tt lj 3!cols[tt] xcol th)) from `t;
update status: first (exec distinct status from t where priorID in changedrowpriorids) from `t where ID in changedrowids;
(1; not (`$()) ~ exec ID from t where priorID in changedrowids;changedrowids)
}
So:
q)t:([]symbol:8#`ABC;receiverIMID:`rep1`rep1`rep2`rep2`rep1`rep1`rep2`rep2;ID:`2019`2018`2019`2018`2019`2018`2019`2018;priorID:`````2018`2017`2018`2017;date:8#.z.Z;priorDate:(0Nz;0Nz;0Nz;0Nz;.z.Z;.z.Z-1;.z.Z;.z.Z-1);status:8#1)
q)t2:([]SYM:2#`ABC;IMID:`rep1`rep2;ID:2#`2017;DATE:2#.z.Z-1;PS:15)
q)\c 200 200
q)t
symbol receiverIMID ID priorID date priorDate status
---------------------------------------------------------------------------------------
ABC rep1 2019 2020.07.01T11:49:39.108 1
ABC rep1 2018 2020.07.01T11:49:39.108 1
ABC rep2 2019 2020.07.01T11:49:39.108 1
ABC rep2 2018 2020.07.01T11:49:39.108 1
ABC rep1 2019 2018 2020.07.01T11:49:39.108 2020.07.01T11:49:39.108 1
ABC rep1 2018 2017 2020.07.01T11:49:39.108 2020.06.30T11:49:39.108 1
ABC rep2 2019 2018 2020.07.01T11:49:39.108 2020.07.01T11:49:39.108 1
ABC rep2 2018 2017 2020.07.01T11:49:39.108 2020.06.30T11:49:39.108 1
q)func\[{x[1]=1b};(0;1b;`)]
0 1b `
1 1b `2018`2018
1 0b `2019`2019
q)t
symbol receiverIMID ID priorID date priorDate status
---------------------------------------------------------------------------------------
ABC rep1 2019 2020.07.01T11:49:39.108 15
ABC rep1 2018 2020.07.01T11:49:39.108 15
ABC rep2 2019 2020.07.01T11:49:39.108 15
ABC rep2 2018 2020.07.01T11:49:39.108 15
ABC rep1 2019 2018 2020.07.01T11:49:39.108 2020.07.01T11:49:39.108 15
ABC rep1 2018 2017 2020.07.01T11:49:39.108 2020.06.30T11:49:39.108 15
ABC rep2 2019 2018 2020.07.01T11:49:39.108 2020.07.01T11:49:39.108 15
ABC rep2 2018 2017 2020.07.01T11:49:39.108 2020.06.30T11:49:39.108 15
Upvotes: 2
terrylynch
Reputation: 13572
I could be mis-interpreting but this looks like a parent-child ID setup, which I would tackle as having a map of parents to children and then follow that map all the way through recursively to its lowest child - and then join that child info. (I think you're doing the same but in reverse?). It's hard to know exactly from your sample. Either way, this might give you an idea:
q)t:([]symbol:8#`ABC;receiverIMID:`rep1`rep1`rep2`rep2`rep1`rep1`rep2`rep2;ID:`2019`2018`2019`2018`2019`2018`2019`2018;priorID:`````2018`2017`2018`2017;date:8#.z.Z;priorDate:(0Nz;0Nz;0Nz;0Nz;.z.Z;.z.Z-1;.z.Z;.z.Z-1);status:8#1);
q)t2:([]SYM:2#`ABC;IMID:`rep1`rep2;ID:2#`2017;DATE:2#.z.Z-1;PS:15);
q)childMap:exec ID!priorID from t where not null priorID; /might want to make this unique
q)(update child:.Q.fu[{x^childMap x}/;ID] from t) lj 3!select symbol:SYM,receiverIMID:IMID,child:ID,status:PS from t2
symbol receiverIMID ID priorID date priorDate status child
---------------------------------------------------------------------------------------------
ABC rep1 2019 2020.07.01T03:54:44.235 15 2017
ABC rep1 2018 2020.07.01T03:54:44.235 15 2017
ABC rep2 2019 2020.07.01T03:54:44.235 15 2017
ABC rep2 2018 2020.07.01T03:54:44.235 15 2017
ABC rep1 2019 2018 2020.07.01T03:54:44.235 2020.07.01T03:54:44.235 15 2017
ABC rep1 2018 2017 2020.07.01T03:54:44.235 2020.06.30T03:54:44.235 15 2017
ABC rep2 2019 2018 2020.07.01T03:54:44.235 2020.07.01T03:54:44.235 15 2017
ABC rep2 2018 2017 2020.07.01T03:54:44.235 2020.06.30T03:54:44.235 15 2017
Upvotes: 2
Related Questions
- How to update kdb table transversely
- KDB How to update column values
- KDB Query to update a column with list
- (KDB/Q+) Update top N rows of a simple table
- Update nested list of tables in KDB table
- Function updating multiple columns in kdb
- KDB/q: How to update a table with row containing disparate columns?
- kdb update statement
- kdb+/q: Apply iterative procedure with updated variable to a column
- (q/kdb+) Update rows using results from previous rows