8 Replies Latest reply: Jan 22, 2018 5:45 AM by Youssef Belloum

# Join Distinct month number on days

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

• ###### Re: Join Distinct month number on days

Hi,

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

• ###### Re: Join Distinct month number on days

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

• ###### Re: Join Distinct month number on days

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;

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;

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
• ###### Re: Join Distinct month number on days

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 -
• ###### Re: Join Distinct month number on days

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

• ###### Re: Join Distinct month number on days

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.

• ###### Re: Join Distinct month number on days

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;

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;

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
• ###### Re: Join Distinct month number on days

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