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

[former_member659241]

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.



 

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.



 

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



 

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



 

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).



 

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.



Data preview of Projection_1



 

2.Convert this Projection into Aggregation





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



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



 

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



Thus, our problem is resolved.

 

Conclusion:

From this blog post you could have got an idea on how to remove duplicates by using aggregation concept. Please feel free to share your views and ideas on the same and post your comments below.

Reference:

‘Excluding Duplicate Records’ from the link provided below: https://answers.sap.com/questions/12271729/exclude-duplicate-records-at-calculation-view.html