I have a pivoted table in Kdb+ with columns state, callable, matureyear, couponrate and rating columns. Rating columns are AAA, AA+, AA, AA- which are pivoted columns with yield as values.
State column has data types symbol and values as states CA, TX, NY, etc. Callable column has data types symbol and values Y or N. Couponrate column has data types float and values values 1, 2, 3, 4, 5, 6. Matureyear column has data types j (long) and values values 1, 2, 3,…,30. AAA, AA+, AA and AA- columns have yield values and float as the data type.
Data:
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 want to fill the missing values for columns AAA, AA+, AA, AA- for the each distinct group (state + callable + couponrate) for matureyear 0 to 30.
I have tried to generate possible combinations of all distinct group.
// Generate all possible combinations of matureyear (0 to 30) for each group
maxYear: 30;
uniqueKeys: distinct select state, callable, couponrate from data;
// Create a table with all possible combinations
// of state, callable, couponrate, and matureyear
allMatureYears:raze {x cross enlist each til maxYear + 1} each uniqueKeys;
allMatureYears: (allMatureYears[`state];
allMatureYears[`callable];
allMatureYears[`couponrate];
allMatureYears[`matureyear]);
However, kdb throws the ‘type error in the cross function.
allMatureYears:distinct select state, callable, couponrate from data;
allMatureYears:update matureyear:count[i]#enlist til maxYear + 1 from allMatureYears
allMatureYears:ungroup allMatureYears
allMatureYears: value flip allMatureYears