Error while creating a calculated view

[former_member937061]

Hi guys, I created an analytic view on a table. I am experiencing the following error(please refer to attached screenshot)-

Short Text

  Attribute type is not valid

Cause

  Mismatch between data type defined and values computed.

Recommendations

  Check calculated measure/attribute definition and ensure data types match.

Error Message

  Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Analytic View: nntransaction rolled back by an internal error: column store error: logical index creation error:  [2052] attribute type is invalid(46)nnSQL: transaction rolled back by an internal error: column store error: <?xml version="1.0" encoding="utf-8"?><createCubeResult version="1.0"><status><message>Error during executing SQL statement</message><errorCode>2116</errorCode></status><details><errors><detail><element>CREATE COLUMN VIEW "_SYS_BIC"."picture_report_BB/INVAUD" WITH PARAMETERS (DESCRIPTION='', INDEXTYPE=5, JOININDEX = "JDA_SRC"."INVAUD", JOININDEXTYPE = 1, VIEWATTRIBUTE = ('ITRSEA', "JDA_SRC"."INVAUD", "ITRSEA", '', '', '', '', 'ITRSEA'), VIEWATTRIBUTE = ('ITVCLC', "JDA_SRC"."INVAUD", "ITVCLC", '', '', '', '', 'ITVCLC'), VIEWATTRIBUTE = ('ITRCUS', "JDA_SRC"."INVAUD", "ITRCUS", '', '', '', '', 'ITRCUS'), VIEWATTRIBUTE = ('ITTPTP', "JDA_SRC"."INVAUD", "ITTPTP", '', '', '', '', 'ITTPTP'), VIEWATTRIBUTE = ('ITRSTY', "JDA_SRC"."INVAUD", "ITRSTY", '', '', '', '', 'ITRSTY'), VIEWATTRIBUTE = ('ITHCOD', "JDA_SRC"."INVAUD", "ITHCOD", '', '', '', '', 'ITHCOD'), KEYFIGURE = ("ITRDAT", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRDAT"), KEYFIGURE = ("ITRTYP", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRTYP"), KEYFIGURE = ("ITRLOC", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRLOC"), KEYFIGURE = ("ITTRET", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITTRET"), KEYFIGURE = ("ITTCST", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITTCST"), KEYFIGURE = ("ITCVAT", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITCVAT"), KEYFIGURE = ("ITRQTY", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRQTY"), KEYFIGURE = ("ITRHOT", 2, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRHOT"), KEYFIGURE = ("ITRVND", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRVND"), KEYFIGURE = ("INUMBR", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="INUMBR"), KEYFIGURE = ("ITRLOC_1", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='', EXPRESSIONFLAGS=0, INDEXID="JDA_SRC"."INVAUD", ATTRIBUTE="ITRLOC"), KEYFIGURE = ("Test", 1, FORMULA='', DESCRIPTION='', UNITCONVERSIONNAME='', EXPRESSION='fixed(if("ITRHOT"=1 AND "ITHCOD"=''USD'' AND "ITRTYP"=11,"ITRQTY",0), 10, 0)', EXPRESSIONFLAGS=1), CHARACTERISTIC=('ITRSEA', KEYATTRIBUTE="ITRSEA"), CHARACTERISTIC=('ITVCLC', KEYATTRIBUTE="ITVCLC"), CHARACTERISTIC=('ITRCUS', KEYATTRIBUTE="ITRCUS"), CHARACTERISTIC=('ITTPTP', KEYATTRIBUTE="ITTPTP"), CHARACTERISTIC=('ITRSTY', KEYATTRIBUTE="ITRSTY"), CHARACTERISTIC=('ITHCOD', KEYATTRIBUTE="ITHCOD"), 'REGISTERVIEWFORAPCHECK'='1', OPTIMIZEMETAMODEL=1)</element><code>46</code><message>transaction rolled back by an internal error: column store error: logical index creation error:  [2052] attribute type is invalid</message></detail></errors></details></createCubeResult>nnCreate OLAP Scenario statement: CREATE OLAP SCENARIO '<?xml version="1.0" encoding="utf-8"?><cubeSchema version="3" operation="createHanaCube"><cube name="_SYS_BIC:picture_report_BB/INVAUD" registerCubeForApsFlag="true" registerCubeForSqlApsFlag="false"><cubeTables><cubeTable name="JDA_SRC:INVAUD" type="f-table"/></cubeTables><joinConditions/><joinPaths/><dimensionAttributes><dimensionAttribute name="ITRSEA"><columnMappings><columnMapping name="ITRSEA" technicalName="ITRSEA" type="key" table="JDA_SRC:INVAUD" column="ITRSEA" isVisible="true"/></columnMappings></dimensionAttribute><dimensionAttribute name="ITVCLC"><columnMappings><columnMapping name="ITVCLC" technicalName="ITVCLC" type="key" table="JDA_SRC:INVAUD" column="ITVCLC" isVisible="true"/></columnMappings></dimensionAttribute><dimensionAttribute name="ITRCUS"><columnMappings><columnMapping name="ITRCUS" technicalName="ITRCUS" type="key" table="JDA_SRC:INVAUD" column="ITRCUS" isVisible="true"/></columnMappings></dimensionAttribute><dimensionAttribute name="ITTPTP"><columnMappings><columnMapping name="ITTPTP" technicalName="ITTPTP" type="key" table="JDA_SRC:INVAUD" column="ITTPTP" isVisible="true"/></columnMappings></dimensionAttribute><dimensionAttribute name="ITRSTY"><columnMappings><columnMapping name="ITRSTY" technicalName="ITRSTY" type="key" table="JDA_SRC:INVAUD" column="ITRSTY" isVisible="true"/></columnMappings></dimensionAttribute><dimensionAttribute name="ITHCOD"><columnMappings><columnMapping name="ITHCOD" technicalName="ITHCOD" type="key" table="JDA_SRC:INVAUD" column="ITHCOD" isVisible="true"/></columnMappings></dimensionAttribute></dimensionAttributes><measures><measure name="ITRDAT" table="JDA_SRC:INVAUD" column="ITRDAT" aggregationType="sum" isVisible="true"/><measure name="ITRTYP" table="JDA_SRC:INVAUD" column="ITRTYP" aggregationType="sum" isVisible="true"/><measure name="ITRLOC" table="JDA_SRC:INVAUD" column="ITRLOC" aggregationType="sum" isVisible="true"/><measure name="ITTRET" table="JDA_SRC:INVAUD" column="ITTRET" aggregationType="sum" isVisible="true"/><measure name="ITTCST" table="JDA_SRC:INVAUD" column="ITTCST" aggregationType="sum" isVisible="true"/><measure name="ITCVAT" table="JDA_SRC:INVAUD" column="ITCVAT" aggregationType="sum" isVisible="true"/><measure name="ITRQTY" table="JDA_SRC:INVAUD" column="ITRQTY" aggregationType="sum" isVisible="true"/><measure name="ITRHOT" table="JDA_SRC:INVAUD" column="ITRHOT" aggregationType="count" isVisible="true"/><measure name="ITRVND" table="JDA_SRC:INVAUD" column="ITRVND" aggregationType="sum" isVisible="true"/><measure name="INUMBR" table="JDA_SRC:INVAUD" column="INUMBR" aggregationType="sum" isVisible="true"/><measure name="ITRLOC_1" table="JDA_SRC:INVAUD" column="ITRLOC" aggregationType="sum" isVisible="true"/><calculatedMeasure name="Test" aggregationType="sum" isVisible="true" calculateBeforeAggregation="true" datatype="fixed" length="10" scale="0"><formula>if("ITRHOT"=1 AND "ITHCOD"=''USD'' AND "ITRTYP"=11,"ITRQTY",0)</formula></calculatedMeasure></measures><constraints/></cube></cubeSchema>'n

[former_member937061]

The following attachment is of the formula used in the calculation field

guys please help

[former_member182114]

For each calculated column click on "Validate Syntax".

[former_member937061]

The syntax is correct

[former_member182114]

Hi Ankur,

Perhaps we can stay all day with one action per reply.

The best help I can offer to you is:

- copy this Analytic view

- try active

- in case of error

- delete a calculated column

- repeat til found no error

- if all calculated column are gone.... look for the columns itself

- remove and activate

- repeat til found no error

Regards, Fernando Da Rós

[DeepakVarandani]

Hi Ankur,

  As ITRHOT is varchar type, can you try comparing values within single quotes. ( '1' )

Regards,

Deepak

[former_member937061]

thanks

[former_member182114]

Hi Ankur,

You didn't posted all the information needed to model for you, anyhow the error message points do you you must do next:

"Cause

  Mismatch between data type defined and values computed.

Recommendations

  Check calculated measure/attribute definition and ensure data types match."

Review your formulas, especially this one:

if("ITRHOT"=1 AND "ITHCOD"=''USD'' AND "ITRTYP"=11,"ITRQTY",0)

Is ITRHOT numeric?

Is ITRTYP numeric?

Review the

[former_member937061]

Hi no they are not numeric. I am using these fields to generate the if logic so that the required data gets used

[former_member937061]

ITRHOT is varchar, ITHCOD is Varchar, ITRTYP is decimal and ITRQTY is also decimal