I have a table with columns state, callable, couponrate, matureyear, rating, yield.
State column has string values as states CA, TX, NY, etc. Callable has two string values Y or N. Couponrate has values 1, 2, 3, 4, 5, 6 as float. Matureyear has values 1, 2, 3,…,30 as integer. Rating has string values AA+, AA-, etc. and yield has float values as the yield.
Data:
State | Callable | Couponrate | Matureyear | Rating | yield
MA | Y | 1 | 2 | AA+ | 4.34 CA | N | 5 | 5 | AA- | 4.64 TX | Y | 3 | 10 | AAA | 4.24 PA | N | 4 | 5 | AA | 4.34 NY | N | 5 | 5 | AA- | 4.64 TX | N | 5 | 10 | AAA | 4.24
Now I want to pivot the data based on rating columns.
When pivoted, index should be state, callable, couponrate and matureyear. columns = rating and values = yield values.
PivotedData should look like below
State | Callable | Couponrate | Matureyear | AAA | AA+ | AA | AA-
MA | Y | 1 | 2 | | 4.34 | |
CA | N | 5 | 5 | | | |4.64
TX | Y | 3 | 10 | 4.24 | | |
PA | N | 4 | 5 | | | 4.34 |
NY | N | 5 | 5 | | | | 4.64
TX | N | 5 | 10 | 4.24 | | |
I have used the query below but it seems like not working and I am getting error for each [each rating] part
//Pivot the table
pivotedTable: exec yield by state, callable, couponrate, matureyear, rating from table; pivotedTable: exec each[rating]!value each[each rating] by state, callable, couponrate, matureyear from pivotedTable;
If your string columns are actually strings, they should probably be symbols (makes pivoting easier). So assuming
q)show t:([]State:`MA`CA`TX`PA`NY`TX;Callable:`Y`N`Y`N`N`N;Couponrate:1 5 3 4 5 5f;Matureyear:2 5 10 5 5 10i;Rating:`$("AA+";"AA-";"AAA";"AA";"AA-";"AAA");yield:4.34 4.64 4.24 4.34 4.64 4.24)
State Callable Couponrate Matureyear Rating yield
-------------------------------------------------
MA Y 1 2 AA+ 4.34
CA N 5 5 AA- 4.64
TX Y 3 10 AAA 4.24
PA N 4 5 AA 4.34
NY N 5 5 AA- 4.64
TX N 5 10 AAA 4.24
then
q)P:exec distinct Rating from t;
q)exec P#Rating!yield by State,Callable,Couponrate,Matureyear from t
State Callable Couponrate Matureyear| AA+ AA- AAA AA
------------------------------------| -------------------
CA N 5 5 | 4.64
MA Y 1 2 | 4.34
NY N 5 5 | 4.64
PA N 4 5 | 4.34
TX N 5 10 | 4.24
TX Y 3 10 | 4.24