cancel
Showing results for 
Search instead for 
Did you mean: 

functional sql - a varying number of columns in a table re-cast to

simon_watson_sj
New Contributor III
Hi All,

I have a table idx which contains a varying number of columns with grouping fields. The columns are named `A through to `C depending how many there are (in practice never more than 3 so far). The final two columns are a timestamp column `t and a float column called `rslt.

I want to replace the grouping columns with 1 column 'C containing a list of values that were formally in separate columns.

To do this for a table with fixed number of grouping columns (say columns `A and `B), I would use: 

?[`idx;();0b;`c`t`rslt!((+:;(enlist;(`idx;enlist`A);(`idx;enlist`B)));`t;`rslt)]

However, since I don't know how many columns there will be, I tried using a list of those grouping columns grpC: `A`B`C

to replace

(`idx;enlist`A);(`idx;enlist`B)

with 

{(`idx;enlist x)} each grpC

This didn't work. 

Does anyone know how I write this function so that I can have a varying number of provided columns all re cast to a list of values in a single column?

Thanks and regards,

Simon
2 REPLIES 2

jwbuitenhuis
New Contributor
Hey Simon

Is the below a step in the right direction?

q)idx:([]A:1 2 3;B:2 3 4;C:3 4 5;D:5 6 7;t:3?.z.p;rslt:3?10.0)
q)idx
A B C D t                             rslt    
----------------------------------------------
1 2 3 5 2008.06.17D15:58:10.379067360 7.85033
2 3 4 6 2010.02.20D07:05:08.677619904 5.347096
3 4 5 7 2011.11.23D02:03:40.566377600 7.111716
q)grpCols:`A`B`C`D
q)update combined:value each grpCols#idx from idx
A B C D t                             rslt     combined
-------------------------------------------------------
1 2 3 5 2008.06.17D15:58:10.379067360 7.85033  1 2 3 5
2 3 4 6 2010.02.20D07:05:08.677619904 5.347096 2 3 4 6
3 4 5 7 2011.11.23D02:03:40.566377600 7.111716 3 4 5 7 

Will

Rolf
New Contributor III

q)f:{@[z _x;y;:;x[;z]]}

 

q)f[idx;`comb;`A`B`C`D]

t                             rslt     comb

----------------------------------------------

2015.06.26D11:34:04.299078848 3.965992 1 2 3 5

2020.07.10D09:13:53.674014208 1.232244 2 3 4 6

2005.07.27D23:37:39.626904096 3.487852 3 4 5 7