How to handle many to many relationship and get most current data in SAP HANA Calculation View?

[Former Member]

Hi Gurus,

I am trying to solve a problem. which i think is caused by a many 2 many 2 many relationship.

I have an EMPLOYEE table which has jobcode, employee, company information.

I have a Jobcode table which has jobcode, jobclass, company, valid_from, valid_to fields.

I have a JOBCLASS table which has jobclass, company and jobclass_Description information.

Business wants to analyse employees historical and current data to see what were the employees jobcode, jobclass during the last 5 years and their changes in jobs (how many technician changed to supervisor etc)

We already have a calculation view with employee, jobcode, company information for analysis but no "jobclass" and jobclass_Description". I need to include these 2 more fields (job_class and job_class description) in the calculation view. So the business can analyse historical and current data for each employee's "Jobcode", "jobclass" and "jobclass_description" based on the employee and his company number, (there are several sub-companies in this organization).

Each company can have many jobcodes and each jobcode can be related to many jobclasses. But when a jobclass id is changed then how we only want to get latest jobclass id

Example below:

Company ||| Jobcode ||| Jobclass ||| Jobclass_description ||| valid_from ||| valid_to

110 123456 00 Technician Jan1, 2002 Current

520 123456 00 Technician Jan1, 2002 Current

220 98765 01 Supervisor Feb 1,2005 dec 3, 2012

220 98765 03 Supervisor Dec 4,2012 Current

So, in the case of company = 220 and jobcode = 98765, employees can have 2 diff jobclasses at different period of time, however we only need the latest/current jobclass id, which in this case will be 03.

1. How can i get the latest jobclass (03) for the employees with jobcode = 98765 and not records for both 01 and 03. This is causing me inflation in number of records compared to the original calculation view.

Any guidance or suggestion will be a great help.

[Former Member]

Thank you all for the suggestions, since my HANA is SPS8, it does not support Rank node function in graphical CV. However, using the filter option on valid_to solved my problem.

Thanks again.

[Former Member]

Hi,

You can use any one of operations below based on functionality and data:

• Filter - If Valid To contains Current as value then filter can help
• Rank Node in CV - Partition by Company and Jobcode, order descending by Valid To and then top 1 of Job class will give desired results.
• Temporal Join - Referential join with temporal properties of current data in between valid from and valid to could help.

Please reply back with concerns.

Thank you.

Best Regards,

Anjali.

,

Hi,

You can try below operations based on your functionality and data:

• If Valid_To shall have Current as values, filter is an option
• Rank node in CV - Descending Order of ValidTo, partition by company and jobcode and then top 1 of job class
• Temporal Join - Referential Join with temporal properties for current date between Valid From and Valid To

Please reply with additional details if this doesn't suffice.

Thank you.

Best Regards,

Anjali.

[former_member208449]

Hello

Can you not use a filter on the valid_to field as current_date? That would fetch only the latest records.