Hi everyone,
I have 2 tables
Table 1:
CIO | Resource Type | Job Title | Date | Headcount |
Person 1 | Contc | PM | 1/31/2015 | 19 |
Person 2 | Emp | M | 2/28/2015 | 20 |
Person 3 | Temp | APM | 1/31/2015 | 1 |
Person 1 | Contc | PM | 1/31/2015 | 5 |
Person3 | Contc | PM | 3/30/2015 | 8 |
Person 1 | Emp | M | 2/28/2015 | 1 |
Table 2:
CIO | Resource Type | Job Title | Date |
Person 1 | Contc | PM | 1/31/2016 |
Person 2 | Emp | M | 1/31/2016 |
Person 2 | Contc | APM | 1/31/2016 |
Person 1 | Emp | PM | 1/31/2016 |
Person 3 | Emp | M | 1/31/2016 |
Person 3 | Emp | PM | 1/31/2016 |
Person 4 | Contc | M | 1/31/2016 |
I want to calculate the ratios and some other things per month. I was done with rest of them except ratios.
so what I have done already is
DetailsTable:
LOAD
Date,
CIO,
[Resource Type] ,
[Job Title] ,
( if([Job Title]= ‘PM' , 1, 0) ) as [Total PM],
FROM
Table 2;
Ratios:
Load
Date,
CIO ,
[Job Title] ,
[Resource Type] ,
SUM([Total PM]) as PM,
--------------------- as M,
--------------------- as APM
COUNT([Job Code]) as Total
Resident DetailsTable
Group by [CIO], Date, [Job Title], [Resource Type];
I have done the count and SUM to plot some-other graphs. I can do it in the UI but there is this requirement to do in the script while loading itself if possible.
In the second step I have loaded
HistoryTable:
LOAD
Concatenate(DetailsTable),
CIO ,
[Resource Type],
[Job Title] as [Job Code],
Date,
SUM( if([Job Title]= PM' , Headcount, 0)) as PM,
------------------------------------------- as M,
------------------------------------------- as APM
SUM(Headcount) as Total
FROM
Table 1
Group by CIO,
[Resource Type],
[Job Title],
Date;
Now to calculate ratios for both years lets say I have this formula
For PM to M ratio. with respect to CIO and date, Lets suppose I have this formula
SUM( total PM + Total M)/Total APM)
what i did was
Tabledata:
Load
MonthYear,
CIO,
If ((Round((1/(([PM]+)/[APM])), 0.1))>0, ('1:'& (Round((1/(([PM]+)/[APM])), 0.1))),'') as [PM to M ratio],
Resident Ratios;
I am getting some values but they are not matching with my manual calculations or the values calculated in excel.
I presume there was some mistake in linking tables or something. but I couldn't figure out where the error is.
Can anyone please help me with this. I hope I am clear with my question.
Thanks in advance.
