complex calculation condition help

[ms_12]

Hi All, I have below dataset.

ID	Is this a project?	Registered	Executor	Hours saved	Team
1	No	Antonio	Damon	6
2	No	Billy	Damon	10
3	Yes	Antonio	Erick	40	Billy, Erick, George
4	No	Cecilia	Faust	2
5	No	Ceclia	George	4

Depending on whether it’s a project or not, I need to calculate how many hours each employee in the company saves.

The calculation must be made under the following conditions:

 If "Is this a project? = Yes" - must be calculated:
1. How many team members are in the project (including the project manager, project manager in my table is "Executor"),
2. Divide the saved project hours by the number of teams,
3. Allocate those hours to each of them.

The result would be:

Antonio	10
Billy	10
Erick	10
George	10

If "Is this a project? = No" - must be calculated:

1. If a person has registered, that person will be assigned 30 percent of the hours saved.
2. If a person is an executor, then that person is assigned 70 percent of the hours saved.

The result would be:

Antonio	1.8	Example: 6*0.3
Billy	3	Example: 10*0.3
Cecilia	1.8	Example: 2*0.3+4*0.3
Damon	11.2	Example: 6*0.7+10*0.7
Faust	1.4	Example: 2*0.7
George	2.8	Example: 4*0.7

Having calculated the data collected by employees, I need to summarize them. The final result should be as follows:

Antonio	11.8
Billy	13
Cecilia	1.8
Damon	11.2
Erick	10
Faust	1.4
George	12.8

@MarcoWedel @RobWunderlich @hic 

[Kushal_Chawda · Accepted solution]

@ms_12  try below

Data:
Load *
Inline [
ID	Is this a project?	Registered	Executor	Hours saved	Team
1	No	Antonio	Damon	6	 
2	No	Billy	Damon	10	 
3	Yes	Antonio	Erick	40	Billy, Erick, George
4	No	Cecilia	Faust	2	 
5	No	Cecilia	George	4 ](delimiter is '\t');

project_no:
Load 
     Capitalize(trim(Registered)) as Employee,
     [Hours saved] * 0.3 as Hours_Saved
Resident Data
where [Is this a project?]='No';

Concatenate(project_no)
Load 
     Capitalize(trim(Executor)) as Employee,
     [Hours saved]* 0.7 as Hours_Saved
Resident Data
where [Is this a project?]='No';

project_yes:
Load ID,
     Capitalize(trim(Registered)) as Employee,
     [Hours saved] as Hours_Saved
Resident Data
where [Is this a project?]='Yes';

Concatenate(project_yes)
Load ID,
     Capitalize(trim(Executor)) as Employee,
     [Hours saved] as Hours_Saved
Resident Data
where [Is this a project?]='Yes';

Concatenate(project_yes)
Load ID,
     Capitalize(trim(SubField(Team,','))) as Employee,
     [Hours saved] as Hours_Saved
Resident Data
where [Is this a project?]='Yes';
  
Drop Table Data;

Concatenate(project_no)
Load Distinct 
     ID,
     Employee,
     Hours_Saved/window(count(DISTINCT Employee),ID) as Hours_Saved
Resident project_yes;

Drop Table project_yes;

Output:
Load Employee,
     sum(Hours_Saved) as Hours_Saved
Resident project_no
Group by Employee;

Drop Table project_no;