I have a table of attributes that I am trying to pivot off of and while the pivot makes sense, several key attribute values I am successfully pivoting off of are prefixed with numbers (for sorting purposes). These are important attributes (there are several like this) that we want to pivot and report on.
I found a similar question here: How to select a column containing dot in column name in kdb and am when I sanitize the dictionary .Q.id t prefixed the columns with a
When I ran type on the returned value it returned 99h so the pivot returns a dictionary.
I'm trying to leverage enlist(`1CODE)#t but to no avail as of yet.
Any thoughts or suggestions?
q) t
monthDate | 1CODE 2CODE 3CODE 4CODE
----------| ------------------------------------
2022.01.01| 18.0054 0.1537228 4.116678 9.332936
2022.02.01| 17.87151 0.1527959 3.866393 9.685012
2022.03.01| 17.739 0.1518747 3.646734 10.00515
...
Tables in kdb are just lists of dictionaries. Type 99h can be both a keyed table and a dictionary. You can still use qsql if you've sanitised your table:
q)select a1CODE from .Q.id t
a1CODE
--------
18.0054
17.87151
17.739
Another option is to use xcol to rename your columns:
q)t:(`monthDate,`$1 rotate'string 1_cols t)xcol t
q)select CODE1 from t1
CODE1
--------
47.35547
75.21426
99.14374