I'm trying to write a function to pivot my tables from wide to long format. so I have something on this lines:
tblwide:([]k1:`a`a`b`b`c`c;xx:1 2 3 4 5 6;yy:11 12 13 14 15 16);
wide2long:{[lhscolnames;rhscolname;varcolname;valuecolname] ?[tblwide;enlist(~:;(^:;rhscolname));0b;((lhscolnames),varcolname,valuecolname)!(eval lhscolnames;enlist rhscolname;rhscolname)] };
tbllong:raze wide2long[enlist `k1;;`index;`val] each (cols tblwide) except `k1;
and that seems to work.
now when I want to have several columns that will not pivot, adapting the code:
tblwide:([]k1:`a`a`b`b`c`c;k2:`t`u`t`u`t`u;xx:1 2 3 4 5 6;yy:11 12 13 14 15 16)
wide2long:{[lhscolnames;rhscolname;varcolname;valuecolname] ?[tblwide;enlist(~:;(^:;rhscolname));0b;((lhscolnames),varcolname,valuecolname)!(eval lhscolnames;enlist rhscolname;rhscolname)] };
tbllong:raze wide2long[`k1`k2;;`index;`val] each (cols tblwide) except `k1`k2;
then it no longer works. it seems q doesnt like the eval .
expected result is, if no mistake on my side is:
expected:([] k1:`a`a`b`b`c`c`a`a`b`b`c`c; k2:`t`u`t`u`t`u`t`u`t`u`t`u ; index:`xx`xx`xx`xx`xx`xx`yy`yy`yy`yy`yy`yy;val:1 2 3 4 5 6 11 12 13 14 15 16)
order is not important really I can always reorder later...
I'm totally open to other simpler/faster solutions, but would still be happy to understand how to solve this issue with the eval.
I don't think you need the eval at all. An approach like this should work for both cases:
wide2long:{[lhscolnames;rhscolname;varcolname;valuecolname] ?[tblwide;enlist(~:;(^:;rhscolname));0b;(lhscolnames,varcolname,valuecolname)!lhscolnames,enlist[enlist rhscolname],rhscolname]};
q)raze wide2long[`k1`k2;;`index;`val] each (cols tblwide) except `k1`k2
k1 k2 index val
---------------
a t xx 1
a u xx 2
b t xx 3
b u xx 4
c t xx 5
c u xx 6
a t yy 11
a u yy 12
b t yy 13
b u yy 14
c t yy 15
c u yy 16