Introduction:
In our projects, we often encounter situations where the obtained data is in a different format than required. To align it with the desired structure, transformation logic must be applied. In this case, the incoming data has monthly values stored in separate columns, which need to be converted into a single value column with multiple rows for each period.
In SAC, this can be accomplished using the simple Transpose Columns to Rows action. However, Datasphere currently does not provide a transpose feature.
Example Scenario:
In this example we are using the COSP table from SAP ECC, where transaction currency values for each period are stored in separate columns. WTG001 for January, WTG 002 for February and so on.
Incoming data: SAP ECC Table COSP
Expected result:
Year
Period
Value
2023
001
4.17
2023
002
4.16
2023
003
4.17
2023
004
4.17
2023
005
4.16
Solution:
We can achieve the desired result in the datasphere using the union operation where the same table is combined with itself.
Steps to implement the same are given below:
Step 1: Load the data to a local table via replication flow.
The target table format in Datasphere, “IL_FI_COSP,” will be an exact replica of the COSP table in SAP ECC. This table will serve as the source for the data flow.
Step 2: Create a dataflow with table “IL_FI_COSP” as the source.
In this case, the two input datasets for the “Union Jan Feb” are projections of the same table, IL_FI_COSP. In “Projection Jan,” select only the fields relevant to January and create a calculated field “Period” to hardcode the value “001” for January.
Similarly, create a projection for February by taking only the relevant fields for February and perform a union operation between Projection Jan and Projection Feb.
Union Output columns:
In the union, include all source columns from the Projection Jan dataset as output columns. From Projection Feb, avoid adding the value fields (WTG002, WKF002 etc.) to the Union output columns. If they are added by default, select only those columns in the union and choose “Delete selected output columns.” This is done to get the January and February values under the same column so that the output of the union will be:
Year
Period
WTG001
2023
001
4.17
2023
002
4.16
Map all columns from Projection Jan to the Union. For Projection Feb, map the February Period Value field to the corresponding field in Union as demonstrated below.
Step 3: In the same manner, create unions for March till December in the same data flow.
Step 4: Combine Union of January and February with Union of March and April.
Similarly, take all the columns from the first data set as the union out put columns. Map the relevant value fields from second dataset to the output columns.
Combine other unions too in a similar fashion till all the columns from January to December are merged.
Step 5: Once all the periods are combined, map the final union to the target table, mapping the value field to the final column (WTGBTR in this case)
Save, deploy and execute the data flow
Final Output:
Alternate Approaches
The same result can be achieved via SQL view or Python script. However, we chose the above method due to its better performance and the need to have the data in a local table for implementing additional business logic.
This approach can also be applied when transitioning from a key figure-based model to an account-based model.
Introduction:In our projects, we often encounter situations where the obtained data is in a different format than required. To align it with the desired structure, transformation logic must be applied. In this case, the incoming data has monthly values stored in separate columns, which need to be converted into a single value column with multiple rows for each period.In SAC, this can be accomplished using the simple Transpose Columns to Rows action. However, Datasphere currently does not provide a transpose feature.Example Scenario:In this example we are using the COSP table from SAP ECC, where transaction currency values for each period are stored in separate columns. WTG001 for January, WTG 002 for February and so on.Incoming data: SAP ECC Table COSPExpected result:YearPeriodValue20230014.1720230024.1620230034.1720230044.1720230054.16 Solution:We can achieve the desired result in the datasphere using the union operation where the same table is combined with itself.Steps to implement the same are given below:Step 1: Load the data to a local table via replication flow.The target table format in Datasphere, “IL_FI_COSP,” will be an exact replica of the COSP table in SAP ECC. This table will serve as the source for the data flow.Step 2: Create a dataflow with table “IL_FI_COSP” as the source.In this case, the two input datasets for the “Union Jan Feb” are projections of the same table, IL_FI_COSP. In “Projection Jan,” select only the fields relevant to January and create a calculated field “Period” to hardcode the value “001” for January. Similarly, create a projection for February by taking only the relevant fields for February and perform a union operation between Projection Jan and Projection Feb.Union Output columns:In the union, include all source columns from the Projection Jan dataset as output columns. From Projection Feb, avoid adding the value fields (WTG002, WKF002 etc.) to the Union output columns. If they are added by default, select only those columns in the union and choose “Delete selected output columns.” This is done to get the January and February values under the same column so that the output of the union will be:YearPeriodWTG00120230014.1720230024.16 Map all columns from Projection Jan to the Union. For Projection Feb, map the February Period Value field to the corresponding field in Union as demonstrated below. Step 3: In the same manner, create unions for March till December in the same data flow.Step 4: Combine Union of January and February with Union of March and April.Similarly, take all the columns from the first data set as the union out put columns. Map the relevant value fields from second dataset to the output columns.Combine other unions too in a similar fashion till all the columns from January to December are merged.Step 5: Once all the periods are combined, map the final union to the target table, mapping the value field to the final column (WTGBTR in this case)Save, deploy and execute the data flowFinal Output: Alternate ApproachesThe same result can be achieved via SQL view or Python script. However, we chose the above method due to its better performance and the need to have the data in a local table for implementing additional business logic.This approach can also be applied when transitioning from a key figure-based model to an account-based model. Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
NEW 
