Combining multiple tables with overlapping Valid from/to dates range into one and completing the timeline in SAP HANA

This blog highlights the steps which can be performed to combine multiple tables having valid from/ valid to dates and completing the timeline without overlapping or missing time range.  This has been achieved through HANA graphical calculation view.

Examples which have been shown here is of HR Data. Let’s take 4 different tables –

PA0000 – HR Master Record: Infotype 0000 (Actions)

PA0001 – HR Master Record: Infotype 0001 (Org. Assignment)

PA0007 – HR Master Record: Infotype 0007 (Planned Working Time)

PA0008 – HR Master Record: Infotype 0008 (Basic Pay)

Data Example of table PA0000

 

Data Example of table PA0001

 

Data Example of table PA0007

 

Data Example of table PA0008

 

The four tables reflect very simple data from HR.

Data from all 4 tables have been combined to complete the timeline without overlapping of any dates. Through the below HANA views, below results will be achieved.

Result Table –

 

Let’s see now how this has been achieved through HANA graphical view.

Two graphical calculation views have been created for this purpose. If required this can also be combined one HANA view.

1^st HANA view – HR_MASTERDATA_CV

Below explains the steps which has been performed in this view –

1. Required column Personnel number, Start Date, End date has been selected from these tables – PA0000, PA0001, PA0007, PA0008 in respective Projection_1, Projection_2, Projection_3 and Projection_4
2. After this the union has been performed for this data. Here PERNR is Personnel Number, BEGDA is Start Date and ENDDA is End Date.

 

The result of the union is as shown below-

 

Duplicate records are highlighted.

1. The aggregation is performed over this data to remove any duplicate data. The result of the aggregation is as below –

 

2. Ranking has been done for these columns based on Start Date (BEGDA) (sorted in ascending order) based on the partitioning on Personnel Number (PERNR). Ranking will be used later to bring the proper timeline together.

 

3. Next step is to create few calculated columns in Projection node. This will provide the new      EndDate column.

Rank2 = “Rank1” –1

ENDDA2 = format(adddays(daydate(“BEGDA”),-1),‘YYYYMMDD’)

 

4. At the final aggregation node, below data can be seen.

 

 

2^nd HANA view – HR_MASTERDATA_TIME_DEPENDENT_CV

In the second HANA view, self-join is performed to achieve the correct timeline.

1. HR_MASTER_DATA_CV is projected to three different projections Projection_2, Projection_3 and Projection_4 as shown below.

 

In Projection_2, below columns as shown have been selected. In this Rank1 and other columns have been selected.

In Projection_3, below columns have been selected. In this Rank2 has been selected instead of Rank1.

 

In Projection_4, below columns have been selected. It is filtered to bring only the last timeline for each personnel number.

 

2. Join is performed between Projection_2 and Projection_3 on the basis of Personnel Number and Ranks.

The data is shown below –

 

As the join is also performed on rank, the most recent record is incomplete as the end date is missing.

3. In project_5 filter is applied to get only those records where EndDate2 is greater than or equal to Start Date.

 

4.To get the complete last record data, ranking is performed on Projection_4 data where the filter was already applied to get only records with EndDate as ‘99991231’.

5. At union_1, union is performed for Projection_5 and Rank_1. This will provide the complete data.

The final data is as shown below. Here, we can see that there are no overlapping records and the timeline is complete.