Saturday, 14 March 2020

Removing Duplicates while fetching Data from Multiple tables in SAP HANA Modelling

Introduction:


While we try to pass data from one table to other in SAP HANA Modelling, there is a possibility for data duplication to occur. In my case, as per the requirement of my customer I wanted to calculate pipeline stock based on order quantity from EKPO table and delivered quantity from MATDOC table. As the data came from these two tables, when we passed data from one table to other table, data duplication occurred. In this blog post, I have discussed the solution for this problem.

Main Problem:


While developing a Material report, I got a requirement to calculate Pipeline stock which is the sum of Order Quantity from EKPO table and Delivered Quantity from MATDOC table. I have mentioned my scenario with screenshots as shown below:

The order quantity for material 100000 was 160 in EKPO table.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

The delivered quantity for material 100000 was -160 in MATDOC table. But because of the field BUDAT it was displayed in two line items for one material number.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

Even after the removal of BUDAT field it had two line items for the same material number.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

After joining the tables EKPO and MATDOC the ordered quantity (MENGE) repeated twice.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

In Semantics, Pipeline stock must be 0 but it was displayed as 160. Because, Pipeline stock = MENGE + Delivered Quantity.

Actual Pipeline Stock value must be 0. Since, 160 – 160 = 0.

But due to duplication, we got 160. (320 – 160 = 160).

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

Solution:

In order to remove the wrong value that has resulted due to the duplicates, I went for a solution. I have mentioned the steps that I followed to achieve the Solution as shown below:

1.Find the Projection that contains duplicate line items and remove the field that is responsible for duplication.In this case it is BUDAT field.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

Data preview of Projection_1

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

2. Convert this Projection into Aggregation

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

3. Add the Measure ‘Delivered quantity’ (Here, ‘Delivered_Stock_Menge’) as Aggregated column. (Note: Add all the measures present here as Aggregated column)

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

4. Now we have only one line item before joining with EKPO table.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

5. So, in the final semantics, Pipeline stock quantity has been achieved in one line item as expected by us.

SAP HANA Studio, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Exam Prep

Thus, our problem is resolved.

No comments:

Post a Comment