on 05-29-2012 9:54 PM
Hi,
I have defined an analytic view based upon a fact table. The fact table stores certain date values as numbers, but they are in a format that corresponds to yyyymmdd. For example, there might be this number: 20120529. I want to turn these back into dates. I tried parsing it out in three pieces, then putting them back together in date format, this way:
calc-attr name: varchar_yyyy
type: varchar 4
expression: if("CLM_CAPITN_ADMIT_DT_SK"='99999','',leftstr("CLM_CAPITN_ADMIT_DT_SK",4))
calc-attr name: varchar_mm
type: varchar 2
expression: if("CLM_CAPITN_ADMIT_DT_SK" ='99999','',midstr("CLM_CAPITN_ADMIT_DT_SK",5,2))
calc-attr name: varchar_dd
type: varchar 2
expression: if("CLM_CAPITN_ADMIT_DT_SK" ='99999','',midstr("CLM_CAPITN_ADMIT_DT_SK",7,2))
calc-attr name: date_conversion_test2
type: date
expression: date("varchar_yyyy","varchar_mm","varchar_dd")
So, the idea is to parse out each piece of the number into strings that correspond to the components of the date, then convert them into a date type. The second parm of each of the first three expressions is two single-quotes in a row, not one double-quote.
These parse fine-- I get "Valid Expression" for each one. However, when I try to activate the view I get the error in the attached text file.
Any advice wil be greatly appreciated. I will turn this into a support case if need be.
I'm using version 28.
TIA,
Steve
Here is the text of the attachment, for those of you who do not have WinRAR:
SAP DBTech JDBC: [2048]: column store error: <?xml version="1.0" encoding="utf-8"?><createCubeResult
version="1.0"><status><message>Inconsistent calculation
model</message><errorCode>34011</errorCode></status><details><errors><detail><element>CalculationNode (finalAggregation) ->
attributes -> calculatedAttribute (varchar_mm) -> expression</element><code>46</code><message>Expression is not valid:
Evaluator: type error in expression
evaluator;TK_STRING_FUNCTION<VALUE_CLASS_STRING>(TK_IF_FUNCTION(TK_EQ[here](TK_ID<"CLM_CAPITN_ADMIT_DT_SK", VALUE_CLASS_INT>,
TK_CONSTANT<"99999", VALUE_CLASS_STRING>), TK_CONSTANT<"", VALUE_CLASS_STRING>,
TK_MIDSTR_FUNCTION(TK_ID<"CLM_CAPITN_ADMIT_DT_SK", VALUE_CLASS_INT>, TK_CONSTANT<"5", VALUE_CLASS_250(1)>, TK_CONSTANT<"2",
VALUE_CLASS_250(1)>)))</message></detail><detail><element>CalculationNode (finalAggregation) -> attributes ->
calculatedAttribute (varchar_dd) -> expression</element><code>46</code><message>Expression is not valid: Evaluator: type error
in expression evaluator;TK_STRING_FUNCTION<VALUE_CLASS_STRING>(TK_IF_FUNCTION(TK_EQ[here](TK_ID<"CLM_CAPITN_ADMIT_DT_SK",
VALUE_CLASS_INT>, TK_CONSTANT<"99999", VALUE_CLASS_STRING>), TK_CONSTANT<"", VALUE_CLASS_STRING>,
TK_MIDSTR_FUNCTION(TK_ID<"CLM_CAPITN_ADMIT_DT_SK", VALUE_CLASS_INT>, TK_CONSTANT<"7", VALUE_CLASS_250(1)>, TK_CONSTANT<"2",
VALUE_CLASS_250(1)>)))</message></detail><detail><element>CalculationNode (finalAggregation) -> attributes ->
calculatedAttribute (varchar_yyyy) -> expression</element><code>46</code><message>Expression is not valid: Evaluator: type
error in expression
evaluator;TK_STRING_FUNCTION<VALUE_CLASS_STRING>(TK_IF_FUNCTION(TK_EQ[here](TK_ID<"CLM_CAPITN_ADMIT_DT_SK", VALUE_CLASS_INT>,
TK_CONSTANT<"99999", VALUE_CLASS_STRING>), TK_CONSTANT<"", VALUE_CLASS_STRING>,
TK_LEFTSTR_FUNCTION(TK_ID<"CLM_CAPITN_ADMIT_DT_SK", VALUE_CLASS_INT>, TK_CONSTANT<"4",
VALUE_CLASS_250(1)>)))</message></detail><detail><element>CalculationNode (finalAggregation) -> attributes ->
calculatedAttribute (date_conversion_test2) -> expression</element><code>46</code><message>Expression is not valid: Evaluator:
type error in expression
evaluator;TK_DAYDATE_FUNCTION<VALUE_CLASS_DAYDATE>(TK_DATE_FUNCTION[here]<VALUE_CLASS_DATE>(TK_ID<"varchar_yyyy",
VALUE_CLASS_STRING(4)>, TK_ID<"varchar_mm", VALUE_CLASS_STRING(2)>, TK_ID<"varchar_dd",
VALUE_CLASS_STRING(2)>))</message></detail></errors><warnings><detail><element>cubeSchema</element><code>46</code><message>Def
ault language not set. Use 'en'</message></detail></warnings></details></createCubeResult>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
So, I figured out a solution a while ago and thought I would share it here, for posterity.
It was simpler than I originally suspected.
Step one:
convert the date-stored-as-an-integer to CHAR 8:
Name: CLM_CAPITN_ADMIT_DT_char
Expression:
string("CLM_CAPITN_ADMIT_DT_SK")
Step two:
convert the CHAR to a date, while accounting for the default value of 99999 and setting it to a default date of 1/1/1900
Name: CLM_CAPITN_ADMIT_DT_date
Expression:
if("CLM_CAPITN_ADMIT_DT_char"='99999',date('1900-01-01'),date("CLM_CAPITN_ADMIT_DT_char"))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.