1

I am quiet new to KDB/Q , I have table having two columns only like below:

id      value
100       a
101       b
100       c
102       d
103       e
100       f
102       g
101       e

I need to convert this table in form of dictionary group by id like in this format

id  | x1   x2  x3  x4  x5  x6  x7  x8 
100 | a    ::  c   ::  ::   f  ::  ::
101 | ::   b  ::   ::  ::   :: ::  e
102 | ::  ::  ::   d   ::   ::  g  ::  
103 | ::  ::  ::   ::   e   f  ::  ::

I tried with by but its giving me only group by id not able convert like keyed table or dictionary

can someone help me in doing the same. Columns name (x1,x2 etc are not important).

sam
  • 47
  • 6

2 Answers2

3

Think maybe you're looking for an exec by from table

q)t:([]id:100 101 100 102 103 100 102 101;val:`a`b`c`d`e`f`g`e)
q)exec val by id from t
100| `a`c`f
101| `b`e
102| `d`g
103| ,`e
q)// if you need the (::)
q)exec {@[count[t]#(::);x;:;y]}[i;val] by id from t
100| `a :: `c :: :: `f :: ::
101| :: `b :: :: :: :: :: `e
102| :: :: :: `d :: :: `g ::
103| :: :: :: :: `e :: :: ::
emc211
  • 1,369
  • 7
  • 14
  • Hi , Thank you for above query,it will group by and return list in value,but I need something like column instead of list , is it possible ? id x1 x2 x3 x4 x5 x6 x7 x8 100| `a :: `c :: :: `f :: :: 101| :: `b :: :: :: :: :: `e 102| :: :: :: `d :: :: `g :: 103| :: :: :: :: `e :: :: :: @emc211 can you help me in this ? – sam Jun 22 '20 at 07:44
  • ```(`$"x",/:string til count t)!/:exec {@[count[t]#(::);x;:;y]}[i;val] by id:id from t``` – emc211 Jun 24 '20 at 21:55
2

It sounds like you want to pivot: https://code.kx.com/q/kb/pivoting-tables/

q)t:([]id:100 101 100 102 103 100 102 101;val:`a`b`c`d`e`f`g`e)
q)exec distinct[t`val]#val!val by id:id from t
id | a b c d e f g
---| -------------
100| a   c     f
101|   b     e
102|       d     g
103|         e
terrylynch
  • 11,844
  • 13
  • 21