Hi Have below scenario --
There are multiple entries for single employee from source . I need to pick latest record and create hierarchy based on that . Below is the example from source --
Emp id | Date | Country | Dept | Sal |
1 | 1/1/2018 | Australia | HR | 100 |
1 | 10/1/2019 | Australia | HR | 150 |
1 | 11/22/2020 | Australia | BD | 200 |
2 | 1/1/2018 | Australia | HR | 300 |
2 | 10/1/2020 | Australia | HR | 350 |
Now for hierarchy creation I want to take the latest entry ,in this example i want to take 3rd entry for emp 1 and 2nd entry for emp 2
Under australia ,emp 1 should have date as 11/22/2020 and dept BD
emp 2 should have date as 10/1/2020 and dept HR
Now for Calculation purpose ,one calculation should be based on latest entry < 1st Nov 20 and other calculation should be based on latest entry irrespective of date .
In this case ,
for emp 1 calculation one should take salary from second entry (with date 10/1/2019) and calculation 2 should take salary from 3rd entry (with date 11/22/2020)
for emp 2 calculation one should take salary from second entry (with date 10/1/2020) and calculation 2 should also take salary from 2nd entry (with date 10/1/2020)
Any help is much appreciated .Thanks in advance !!