Problem with creating a calculated attribute in an analytic view in HANA Studio

[Former Member]

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

HANA-error.txt.zip

[Former Member · Accepted solution]

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>

[Former Member · Accepted solution]

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"))