I have 2 lists that contain 50 tables per list. Each table in each list has a column called ID...
count list1
50
count list2
50
I'd like to be able to perform a left-join on each table in the list to each table in the second list (using the ID column as the key column) and then save the results of each join in a separate variable for each join.
I can perform a left-join on each table individually (using the table at index 27 as an example) by doing the following. This gives the desired results for the table at the 27th index...
table27:list1[27] lj `ID xkey list2[27]
How can I iterate through the list of tables, joining each table at their respective indices and storing that joined table to a variable before performing the same operation on the next set of tables?
To join the tables so far I've tried the following...
joinedLists:each list1 lj `ID xkey each list2
joinedLists:(each list1) lj `ID xkey each list2
joinedLists:(each list1) lj' `ID xkey each list2
joinedLists:lj/[(each list1;each list2)]
joinedLists:lj/'[(each list1;each list2)]
joinedLists:list1[1+til 51] lj `ID xkey list2[1+til 51]
Though all of the above queries, except for the last, result in the cryptic error message '. The last query I tried gives a result but upon further inspection of the tables in that list it's missing a column and the counts aren't what is expected...
count each list1
913930 799359 53212 20 70 6 116 8 139 16 74 28 3 69 11 137 6 22 42 123 5 23 16 1 13 26 26 9 93 11 2 1 2 2 14 2 1 15 3 11 25 7 4 3 1 2 6 9 1 2 1
count each list2
274506 440675 44935 20 70 6 101 6 138 15 71 27 3 63 10 133 6 21 42 123 3 16 11 1 12 25 26 13 92 10 2 1 2 2 14 2 1 15 3 11 25 7 4 3 1 2 6 30 1 2 1
count each joinedLists
799410 53263 71 121 57 167 59 190 67 125 79 54 120 62 188 57 73 93 174 56 74 67 52 64 77 77 60 144 62 53 52 53 53 65 53 52 66 54 62 76 58 55 54 52 53 57 60 52 53 52 51
An example below with two tables in each list:
q)list1:(([]ID:1 2 3;b:4 5 6);([]ID:6 7 8;b:9 10 11))
q)list2:(([]ID:1 2 3;b:14 15 16);([]ID:6 7 8;b:19 20 21))
q){[t1;t2] list3,:enlist t1 lj `ID xkey t2}'[list1;list2];
q)list3
+`ID`b!(1 2 3;14 15 16)
+`ID`b!(6 7 8;19 20 21)
each both (') is used to apply the function item-wise on both lists.
,: is used to globally join each enlisted result and assign to the variable list3