Hi Everyone,
I my scenario, i have 3 excel source in different data format
excel one contains data in the format
[SR]:
| Manager Name | Employee Name | Month | Quarter | Comm Type |
|---|
| Hardik | Priyanka | 1 | 1 | First |
| Hardik | Monika | 1 | 1 | Pre |
| Hardik | Sarvesh | 1 | 1 | First |
| Hardik | Afsar | 1 | 1 | First |
| Hardik | Ram | 1 | 1 | Pre |
| Hardik | Sita | 1 | 1 | Pre |
| Anis | Akhilesh | 1 | 1 | Pre |
| Anis | Ramesh | 1 | 1 | First |
| Anis | Saravana | 1 | 1 | First |
| Anis | Prashant | 1 | 1 | First |
| Anis | Prathamesh | 1 | 1 | Pre |
| Anis | Aman | 1 | 1 | Pre |
| Anis | Rahul | 1 | 1 | Pre |
| Hardik | Priyanka | 2 | 1 | First |
| Hardik | Monika | 2 | 1 | First |
| Hardik | Sarvesh | 2 | 1 | Pre |
| Hardik | Afsar | 2 | 1 | Pre |
| Hardik | Ram | 2 | 1 | Pre |
| Hardik | Sita | 2 | 1 | Pre |
| Anis | Akhilesh | 2 | 1 | Pre |
| Anis | Ramesh | 2 | 1 | First |
| Anis | Saravana | 2 | 1 | Pre |
| Anis | Prashant | 2 | 1 | Pre |
| Anis | Prathamesh | 2 | 1 | First |
| Anis | Aman | 2 | 1 | First |
| Anis | Rahul | 2 | 1 | First |
in the above table we have to load where comm type = 'Pre'
[Paid]:
| Manager Name | Employee Name | Month | Quarter | count |
|---|
| Hardik | Priyanka | 1 | 1 | 1 |
| Hardik | Sarvesh | 1 | 1 | 1 |
| Hardik | Ram | 1 | 1 | 0 |
| Hardik | Sita | 1 | 1 | 1 |
| Anis | Akhilesh | 1 | 1 | 0 |
| Anis | Ramesh | 1 | 1 | 1 |
| Anis | Saravana | 1 | 1 | 1 |
| Anis | Prashant | 1 | 1 | 0 |
| Anis | Prathamesh | 1 | 1 | 1 |
| Anis | Aman | 1 | 1 | 1 |
| Anis | Rahul | 1 | 1 | 0 |
| Hardik | Priyanka | 2 | 1 | 1 |
| Hardik | Sarvesh | 2 | 1 | 1 |
| Hardik | Ram | 2 | 1 | 0 |
| Hardik | Sita | 2 | 1 | 1 |
| Anis | Akhilesh | 2 | 1 | 1 |
| Anis | Ramesh | 2 | 1 | 0 |
| Anis | Saravana | 2 | 1 | 0 |
| Anis | Prashant | 2 | 1 | 0 |
| Anis | Prathamesh | 2 | 1 | 0 |
| Anis | Aman | 2 | 1 | 0 |
| Anis | Rahul | 2 | 1 | 1 |
| Hardik | Payal | 3 | 1 | 0 |
| Hardik | Mohan | 3 | 1 | 4 |
and score table as shown in first message.
Score of every employee is calculated as follows Sum(Count) / count(Comm Type).
I am unable to achieve this in backend and that the reason i calculated it straight table and want to map each partners value in frontend
