topic Re: Join Distinct month number on days in App Development

Join Distinct month number on days

pauldamen — Mon, 22 Jan 2018 09:37:38 GMT

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

YoussefBelloum — Mon, 22 Jan 2018 10:07:07 GMT

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

pauldamen — Mon, 22 Jan 2018 10:08:43 GMT

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

YoussefBelloum — Mon, 22 Jan 2018 10:27:51 GMT

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

Re: Join Distinct month number on days

pauldamen — Mon, 22 Jan 2018 10:30:07 GMT

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

YoussefBelloum — Mon, 22 Jan 2018 10:34:55 GMT

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

Re: Join Distinct month number on days

pauldamen — Mon, 22 Jan 2018 10:37:54 GMT

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

YoussefBelloum — Mon, 22 Jan 2018 10:39:07 GMT

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

Re: Join Distinct month number on days

YoussefBelloum — Mon, 22 Jan 2018 10:45:19 GMT

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