Calculated column in SAP HANA
If you have made it this far into the tutorial, I commend your will to continue striving to be the best in SAP HANA. This website will continue to focus on teaching you everything that you need to know to be an excellent SAP HANA consultant. Moving on, this tutorial will help you understand how to create new fields in views which are calculated based on some logic which may involve already existing fields in the table. Such a field is called Calculated Column in SAP HANA.
Let’s build a Calculated Column with an example
To start, open the calculation view we are working on and click on Projection_1 node. Let’s create a new field here that would be called SALE_TYPE and the logic would be to concatenate fields AUART and WAERK separated by a dash symbol. Right click on the “Calculated Columns” folder and click “New”.
The below dialog box opens up where you need to give the new field a technical name, a datatype and the logic inside the “Expression Editor”.
After adding the name and datatype as shown below, double click on the AUART field to add it to the “Expression Editor”.
It gets added as shown below.
Concatenate more strings to it using the + operator. The Plus (+) operator works as a string concatenator for string fields and an arithmetic plus for numerical datatypes like decimal and integers. We needed the AUART and WAERK to be separated by a dash. Hence after the plus symbol, we add a dash surrounded by single quotes since it is a character value. Then add another plus for the next field WAERK. The end result should resemble the below. Double click WAERK to add it at the end too.
Once the expression is complete as below, press the “Validate Syntax” to verify the syntax of your code.
The below information message confirms that the code is correct.
Press OK and save and you would come out of this editor. Now as you see below, a new Calculated column SALE_TYPE has been created successfully.
Now, as you might have realized, this field is only available in Projection_1. It needs to be propagated to all nodes above it. To do this, click on the node above it which is the Join_1 node in our case. Find the field and right click on it and select “Propagate to Semantics” from the context menu.
You get an information message outlining the nodes to which this field has been propagated to. Press OK.
Activate the view and run a data preview. As seen below, our SALE_TYPE field has appeared and seems to be running as per our calculation logic.
Let’s try another one. This time let’s create a new calculated column at the “Aggregation Node” for a numerical field- NETWR. The new requirement is to create a field NORM_VAL which would be equal to NETWR divided by 12 showing up to 2 decimal places. Once again, right click on the “Calculated Columns” folder and click on “New Calculated Column” from the context menu.
A new window pops up where we fill the details as we before. The name, A description and datatype need to be filled. Since the value when divided by 12 will surely return decimal values, we mark Datatype as Decimal with a maximum length of 10 before the decimal. A scale of 2 tells the system to only display the result up to 2 decimal places. Also, this time, make sure you switch the Column type to “Measure” since this is a transaction field. If you don’t remember what a measure is and how it is different from an attribute, revisit the Master data vs Transaction data tutorial. In the expression editor, enter the formula as NETWR/12 as required and press OK.
As seen below, the new field has been created successfully.
Since this is on the last aggregation node, there is no need to propagate it further. Save and activate this view. Press data preview to check the values as shown below.
This concludes our tutorial on SAP HANA Calculated columns. Hope you guys found it interesting. Comment below with your thoughts.
Please show your support for this website by sharing these tutorials on social media by using the share buttons below. Stay tuned for the next ones.