Dynamically consolidate columns into one column with a list of values

Question

I have a table idx which each time I build it can consist of a number of grouping columns labeled `A `B `C. There are usually around 2 or three of these columns (so `A `B or `A `B `C) but in theory could be more. There is also a timestamp column `t and a float column `rslt.
I wish to recast the grouping columns so that I have 1 column with a list of values which were in the previous grouping columns. For a fixed number of columns (say 2 - `A `B), I would use:
?[`idx;();0b;`c`t`rslt!((+:;(enlist;(`idx;enlist`A);(`idx;enlist`B)));`t;`rslt)]

However, since the number of grouping columns can vary, I had hoped to replace
(`idx;enlist`A);(`idx;enlist`B)

With
{enlist (`idx;enlist x)} each aCols

Where aCols is a list of the columns. (so aCols:`A`B )
?[`idx;();0b;`c`rslt!((+:;(enlist;{(`idx;enlist x),} each aCols));`rslt)]

This did not work.
I notice that the parse function shows that the brackets aren't rendered in the Developer GUI so my code gives:
`idx ,`A
`idx ,`B

rather than the 'static' function which gives:
(`idx ,`A)
(`idx ,`B)

Is this the source of the problem? How come the list I build isn't demarcated by an open and closing bracket in the console printout?
Could you advise where I might be going wrong?
Thanks and regards,
Simon

terrylynch · Answer

If the data looks like:
idx:([]t:5?.z.p;A:5?.Q.A;B:5?.Q.A;C:5?.Q.A;rslt:5?10f)

then your functional can become:
?[`idx;();0b;`c`t`rslt!((+:;enlist,{(`idx;enlist x)}each aCols);`t;`rslt)]

but a cleaner functional approach might be
q)?[`idx;();0b;`c`t`rslt!((,';;)/[`A`B`C];`t;`rslt)]
c     t                             rslt
--------------------------------------------
"NGJ" 2009.01.10D17:16:43.689503360 3.927524
"JBL" 2011.11.04D08:07:14.231674368 5.170911
"CYF" 2012.03.03D11:18:21.556469184 5.159796
"HFU" 2017.06.10D02:01:53.890645696 4.066642
"AEW" 2008.01.01D00:10:18.538771776 1.780839

creilly · Answer

It's not an answer to your question but you can achieve the result you're after without the need for a functional update here. Note I've made a guess at data for the below table.
q)show idx:([]t:5?.z.p;rslt:5?10f;A:5?10;B:5?10;C:5?10)
t                             rslt     A B C
--------------------------------------------
2010.12.31D03:08:15.054082816 2.124007 9 0 8
2009.05.26D07:50:10.992718592 7.77882  5 9 6
2000.03.01D12:40:41.350664308 4.844727 9 5 9
2002.02.19D06:32:27.078053856 6.827999 7 2 0
2002.02.23D03:39:47.650230800 1.53227  6 3 0
q)f:{(y _x),'flip enlist[`c]!enlist flip x y:(),y}
q)f[idx;`A`B`C]
t                             rslt     c
--------------------------------------------
2010.12.31D03:08:15.054082816 2.124007 9 0 8
2009.05.26D07:50:10.992718592 7.77882  5 9 6
2000.03.01D12:40:41.350664308 4.844727 9 5 9
2002.02.19D06:32:27.078053856 6.827999 7 2 0
2002.02.23D03:39:47.650230800 1.53227  6 3 0
q)f[delete A,B from idx;`C]
t                             rslt     c
----------------------------------------
2010.12.31D03:08:15.054082816 2.124007 8
2009.05.26D07:50:10.992718592 7.77882  6
2000.03.01D12:40:41.350664308 4.844727 9
2002.02.19D06:32:27.078053856 6.827999 0
2002.02.23D03:39:47.650230800 1.53227  0

Dynamically consolidate columns into one column with a list of values

2 Answers

Add your own answers!

Ask a Question