Calculation view model - Transpose values

[ssurampally]

Hi Experts,

I need to create a report on EQUI, KSML, AUSP tables, to list out the equipment which meets the selection criteria on characteristics and it's values.

I am able to make the join conditions fine, but during testing it is noticed that characteristic values in AUSP are stored in one column,

so it will be as below,

Equipment	Equipment type	Characteristic	Characteristic value
400010	Bridge	Char 1	Char value 1
400010	Bridge	Char 2	Char Value 2
400010	Bridge	Char 3	Char Value 3

With that, I am unable to apply the selection criteria on char 1 and char 2 value combination, for this I will have to store the data in different columns, one column for each characteristic value. as below,

Equipment	Equipment type	Char 1	Char 2	Char 3
400010	Bridge	Char 1 Value	Char 2 value	Char 3 Value

there is no other table which stores the data in this way.So I will have to get the data available in this format, during HANA modeling. I created 3 calculated columns and used simple formulas to populate the values,

     char 1 formula   is if characteristic = char 1 then update char 1 value

the output is displayed in the following way,

Equipment	Equipment type	Char 1	Char 2	Char 3
400010	Bridge	Char 1 value	Blank	Blank
400010	Bridge	Blank	Char 2 Value	Blank
400010	Bridge	Blank	Blank	Char 3 value

But I would need to display them in one row for report on this model.

Can you suggest me the best way to do the transpose of rows to columns and make them in one row?  is there a way to achieve by doing any other projections and aggregations in the calculation view?  Please let me know.

[former_member200930 · Accepted solution]

Sreekanth,

Please use STRING_AGG function and go with scripted calculation view, not graphical calculation view.

Please check following link for more details.

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a4389775191014b5a6bf2ccc0df2ed/content.htm#l...

[ssurampally · Accepted solution]

Hi Vikram, STRING_AGG function doesn't make sense, I am not doing any aggregation on rows, it is all characteristics.

I will explore more on Scripting, using loop or similar statements to get this requirement.

if any one has done this already, please share your inputs.

[Former Member · Accepted solution]

Hi Sreekanth,

You can get one row instead of multiple using Aggregation node in graphical calculation view.

if you want this model as dimension, then add an aggregation node before default projection.

Add all your calculated columns as measures to aggregation node output and change aggregation type to 'MAX'

Now in your default projection add them as attributes to semantics.

Regards,

Venkat N.

[ssurampally · Accepted solution]

this works fine for values which are defined as decimals and integers, there are characteristic values with varchar type also, I am getting the multiple rows for those ones.

[ssurampally · Accepted solution]

Hi Venkat,

for the non-numeric ones I used the aggregation type 'Count' seems to be working. just playing round, not sure how exactly working.  I will do the detailed test and update you with the final result.

[Former Member · Accepted solution]

Hi Sreekanth,

MAX function should work fine for varchar values too. Please make sure you have selected MAX as aggregation type.

Sometime even though we select MAX aggregation type, once you activate or click else where in HANA studio. system changes that to aggregation type next to MAX in settings.

Please check that.

Regards,

Venkat N.

[ssurampally · Accepted solution]

You are right, aggregation type 'Max' is working fine for char values also. now all the values are in one line.  When I first tested it, for reason I see the char values in multiple lines. Anyway it is good now.

Thank you.