Modelling: Column to Row Transpose using Projection
Abani Pattanayak, SAP HANA COE (North America)
Martin Donadio, NGS Hana COE (Argentina)
This is an alternative approach to Column to Row transpose discussed in Modelling: Column to Row Transpose using Matrix in HANA. In this article we'll use multiple projection steps in a calculation view to transpose from column to row.
The following is output of Analytic View (AN_DATA)
We'd like to transform the dataset (from column to row) as shown below
1. Build the calculation view as below. Insert one Project Node for each column (3 projections for "AGR", "OIL" and "TAX").
2. Rename the Column "AMOUNT_OIL" to "AMOUNT" in Projection_OIL (highlighted in yellow in the above picture). Similarly rename 'AMOUNT_AGR" and "AMOUNT_TAX" in the other two projections
3. In the Union node, create a new target "AMT_TYPE" and map as below.
4. Expose the DOCID, COUNTRY, AMT_TYPE and AMOUNT in the Aggregation node and activate the calculation view.
Now execute the following Query for the desired output