Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I have 2 tables; 1 table with actual hours people worked per day, and 1 table with scheduled working hours per month:
Actual
EmployeeID Day Hours
1 1-1 8
1 1-2 8
2 1-1 8
2 1-2 8
3 1-1 8
3 1-2 8
3 1-3 8
Schedule
EmployeeID Month Hours
1 Jan 184
2 Jan 184
3 Jan 184
How can I join these 2 tables together? If I left Join it will join the total per month on every day. If I join Distinct it will only join 1 time for an employee. What I need is a distinct Join but then distinct per month. How can I achieve this?
Regards, Paul
Hi,
on the first table, Day column value 1-3 corresponds to what exactly ? the first number and the second number
That is a day date, wrote it in American date style. January 1st, January 2nd, January 3rd etc.
So maybe you can try this:
Actual:
LOAD EmployeeID, left(Day,1) as Month, TextBetween(Day,'-','') as Day, Hours, EmployeeID&'_'&left(Day,1) as Key;
LOAD * Inline [
EmployeeID,Day,Hours
1,1-1,8
1,1-2,8
2,1-1,8
2,1-2,8
3,1-1,8
3,1-2,8
3,1-3,8
];
Schedule:
LOAD EmployeeID&'_'&num(month(date#(Month,'MMM'))) as Key, Hours as schedule_Hours;
LOAD * Inline [
EmployeeID,Month,Hours
1,Jan,184
2,Jan,184
3,Jan,184
];
what i have done here:
formatted the Month field on the schedule table to have the month number
extracted the month number from day field from the Actual table
created a key EmployeeID & Month as Key
Renamed Hours field on the schedule table to SCHEDULE HOURS
here is the result:
EmployeeID | Month | Day | Hours | schedule_Hours |
1 | 1 | 1 | 8 | 184 |
2 | 1 | 1 | 8 | 184 |
3 | 1 | 1 | 8 | 184 |
1 | 1 | 2 | 8 | 184 |
2 | 1 | 2 | 8 | 184 |
3 | 1 | 2 | 8 | 184 |
3 | 1 | 3 | 8 | 184 |
Thanks for the reply, but this result is not what I am looking for.
The schedule hours should only be displayed on 1 day, since this is a month total. So the table should look like:
EmployeeID | Month | Day | Hours | schedule_Hours |
1 | 1 | 1 | 8 | 184 |
2 | 1 | 1 | 8 | 184 |
3 | 1 | 1 | 8 | 184 |
1 | 1 | 2 | 8 | - |
2 | 1 | 2 | 8 | - |
3 | 1 | 2 | 8 | - |
3 | 1 | 3 | 8 | - |
if scheduled hours is Month total, why display it on day 1 only ?
Because all aggregations we make are on monht level. So we add up all the days and compare to month total. If the month total is displayed on everyday you would get 30 * 184 as scheduled total.
here I added the day on the key:
Actual:
LOAD EmployeeID, left(Day,1) as Month, TextBetween(Day,'-','') as Day, Hours, EmployeeID&'_'&left(Day,1)&'_'&TextBetween(Day,'-','') as Key;
LOAD * Inline [
EmployeeID,Day,Hours
1,1-1,8
1,1-2,8
2,1-1,8
2,1-2,8
3,1-1,8
3,1-2,8
3,1-3,8
];
Schedule:
LOAD EmployeeID&'_'&num(month(date#(Month,'MMM')))&'_'&'1' as Key, Hours as schedule_Hours;
LOAD * Inline [
EmployeeID,Month,Hours
1,Jan,184
2,Jan,184
3,Jan,184
];
Result:
EmployeeID | Month | Day | Hours | schedule_Hours |
1 | 1 | 1 | 8 | 184 |
2 | 1 | 1 | 8 | 184 |
3 | 1 | 1 | 8 | 184 |
1 | 1 | 2 | 8 | |
2 | 1 | 2 | 8 | |
3 | 1 | 2 | 8 | |
3 | 1 | 3 | 8 |
You can also replicate schedule_Hours for all the lines like mentioned above and use Aggr() function to group by your hours by the Key, like this:
Aggr(sum(schedule_Hours), Key)
like that you will have you scheduled hours my month
for the month 1, you will have 184