I have a table with (28) columns that hold values for each month of the current year, each month of next year and the 4 future years. The columns are named CY_1 thru CY_12, NY_1 thru NY_12, and FY_2 thru FY_5.
After I used an Unpivot Transformation, each of these column headers have become a value in the new Pivot Key Value field, as expected. But I am now trying to create a derived column that represents those values as dates so I can eventually tie it to the dim_Date table. I tried to start by deriving the year.
I am using this expression in a Derived Column transformation to extract the year (based on another field, Fcast_Year) but it is failing:
Am I approaching this correctly? If so, what is wrong with this expression? I cast the string field [Fcast_Year] as numeric... I don't understand why it doesn't accept it.
After I used an Unpivot Transformation, each of these column headers have become a value in the new Pivot Key Value field, as expected. But I am now trying to create a derived column that represents those values as dates so I can eventually tie it to the dim_Date table. I tried to start by deriving the year.
I am using this expression in a Derived Column transformation to extract the year (based on another field, Fcast_Year) but it is failing:
Code:
LEFT( [PivotKeyValue], 2) == "CY"? (DT_NUMERIC, 4,0 ) [Fcast_Year] : LEFT( [PivotKeyValue], 2) == "NY" ? ((DT_NUMERIC, 4,0 ) [Fcast_Year] + 1) : LEFT( [PivotKeyValue], 2) == "FY" ? ((DT_NUMERIC, 4,0 ) [Fcast_Year] + RIGHT( [PivotKeyValue], FINDSTRING([PivotKey Value],"_",1 )): 0000Am I approaching this correctly? If so, what is wrong with this expression? I cast the string field [Fcast_Year] as numeric... I don't understand why it doesn't accept it.