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

Community Support
1

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;ctrslt!((+:;(enlist;(idx;enlistA);(idx;enlistB)));t;rslt)]

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

to replace

(idx;enlistA);(idx;enlistB)

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

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:ABCD
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

3

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