I want to join the following 3 tables row-wise, but also while moving some columns.
q)
newtab1:([]basecol1:10 20 30;basecol2:40 50 60;L1Col1:"a","b","c";L1Col2:"d","e","f";L1Col3:"g","h","i")
newtab2:([]basecol1:100 200 300;basecol2:400 500 600;L1Col1:"a","b","c";L1Col2:"d","e","f";L2Col1:"j","k","l";L2Col2:"m","n","o";L1Col3:"g","h","i";L2Col3:"p","q","r")
newtab3:([]basecol1:1 2 3;basecol2:4 5 6;L1Col1:"a","b","c";L1Col2:"d","e","f";L2Col1:"j","k","l";L2Col2:"m","n","o";L3Col1:"s","t","u";L3Col2:"v","w","x";L1Col3:"g","h","i";L2Col3:"p","q","r";L3Col3:"y","z","z")
q)newtab3
basecol1 basecol2 L1Col1 L1Col2 L2Col1 L2Col2 L3Col1 L3Col2 L1Col3 L2Col3 L3Col3
--------------------------------------------------------------------------------
1 4 a d j m s v g p y
2 5 b e k n t w h q z
3 6 c f l o u x i r z
That is, "Col3" for each "L" should be moved to the end
I want to fill all the columns that don't have data with zeroes. That is, I need to add 3 columns to table 1 (and move one column), and 6 columns to table 2 (and move 2 columns). The columns are always labeled with this same pattern. I started by creating the column names like this:
LegColNames:("Col1";"Col2";"Col3")
L1LegColNames: "L1",/:legColNames
but this makes them a string, how do I get those to be column names?
FWIW, in the real implementation, this will be joining hundreds of tables, and I'll need to take the table with the maximum number of Ls, and add the appropriate numbers of columns of zeroes to each table to make it equal that maximum. It can go up as high as L13.
Always break these things down into steps:
The last part is the only bit that will be more difficult for many more tables. You'll want to parse the ending number and then find the sorted indices of the columns.