I have a certain dataset that I have selected as below:
tbl: select qtix by name, ptype from data
Which yields the following result:
| name | ptype | qtix |
-----------------------
| ABCD | `BLUE | 132 |
| PQRS | `BLUE | 140 |
| PQRS | `RED | 260 |
| VXYZ | `BLUE | 116 |
...
I have done so as this dataset is to be filtered based on ptype later on but for now I am wanting to put it back together such that name is the only distinct field, i.e.:
| name | ptype | qtix |
-----------------------------
| ABCD | `BLUE | 132 |
| PQRS | `BLUE`RED | 400 |
| VXYZ | `BLUE | 116 |
...
I have attempted to do this via:
tbl: select by name from tbl
But instead this only chooses one of the two rows to display instead of joining them together, however I am unsure how to perform a join on a row basis instead of for columns.
Any help would be greatly appreciated!
You can use the keyword ungroup. (https://code.kx.com/q/ref/ungroup/)
q)show tbl:([] name:`a`c`c`a; ptype:`blue`blue`red`blue; qtix:4?10)
name ptype qtix
---------------
a blue 7
c blue 1
c red 9
a blue 1
q)show tbl:select qtix by name,ptype from tbl
name ptype| qtix
----------| ----
a blue | 7 1
c blue | ,1
c red | ,9
q)show tbl:ungroup tbl
name ptype qtix
---------------
a blue 7
a blue 1
c blue 1
c red 9
q)show tbl:select ptype,qtix by name from tbl
name| ptype qtix
----| --------------
a | blue blue 7 1
c | blue red 1 9
q)show tbl:ungroup tbl
name ptype qtix
---------------
a blue 7
a blue 1
c blue 1
c red 9
Do note that (from the docs):
ungroup is not the exact inverse of grouping
Grouping sorts on the keys, so a subsequent ungroup returns the original records sorted by the grouped column/s.
Also note that if you do:
tbl: select by name from tbl
You are only selecting the last entry of the name.