Join Distinct month number on days

[pauldamen]

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

[YoussefBelloum]

Hi,

on the first table, Day column value 1-3 corresponds to what exactly ? the first number and the second number

[pauldamen]

That is a day date, wrote it in American date style. January 1st, January 2nd, January 3rd etc.

[YoussefBelloum]

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

[pauldamen]

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	-

[YoussefBelloum]

if scheduled hours is Month total, why display it on day 1 only ?

[pauldamen]

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.

[YoussefBelloum]

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

[YoussefBelloum]

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