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

Client Personnel number Start Date End Date
100 00000615 19810831 20160625
100 00000615 20160626 99991231

 

Data Example of table PA0001

Client Personnel number Start Date End Date
100 00000615 19810831 20160625
100 00000615 20160626 20170228
100 00000615 20170301 99991231

 

Data Example of table PA0007

Client Personnel number Start Date End Date
100 00000615 20160626 99991231

 

Data Example of table PA0008

Client Personnel number Start Date End Date
100 00000615 20160626 20160917
100 00000615 20160918 99991231

 

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 –

Client Personnel number Start Date End Date
100 00000615 19810831 20160625
100 00000615 20160626 20160917
100 00000615 20160918 20170228
100 00000615 20170301 99991231

 

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.

1st 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-

Personnel number Start Date End Date
00000615 19810831 20160625
00000615 20160626 99991231
00000615 19810831 20160625
00000615 20160626 20170228
00000615 20170301 99991231
00000615 20160626 99991231
00000615 20160626 20160917
00000615 20160918 99991231

 

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 –

 

Personnel number Start Date End Date
00000615 19810831 20160625
00000615 20160626 99991231
00000615 20160626 20170228
00000615 20170301 99991231
00000615 20160626 20160917
00000615 20160918 99991231
  1. 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.

 

Personnel number StartDate EndDate EndDate2 Rank2 Rank1
00000615 19810831 20160625 19810830 0 1
00000615 20160626 99991231 20160625 1 2
00000615 20160626 20170228 20160625 2 3
00000615 20160626 20160917 20160625 3 4
00000615 20160918 99991231 20160917 4 5
00000615 20170301 99991231 20170228 5 6

 

2nd 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 –

Personnel number StartDate EndDate2 Rank1
00000615 19810831 20160625 1
00000615 20160626 20160917 2
00000615 20160918 20170228 3
00000615 20170301 ? 4

 

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.

Personnel number StartDate EndDate2
00000615 19810831 20160625
00000615 20160626 20160917
00000615 20160918 20170228
00000615 20170301 99991231

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply