Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

pauldamen
Contributor II

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

8 Replies
YoussefBelloum
Esteemed Contributor

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

pauldamen
Contributor II

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.

YoussefBelloum
Esteemed Contributor

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;

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:

     

EmployeeIDMonthDayHoursschedule_Hours
1118184
2118184
3118184
1128184
2128184
3128184
3138184
pauldamen
Contributor II

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:

EmployeeIDMonthDayHoursschedule_Hours
1118184
2118184
3118184
1128-
2128-
3128-
3138-
YoussefBelloum
Esteemed Contributor

Re: Join Distinct month number on days

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

pauldamen
Contributor II

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.

YoussefBelloum
Esteemed Contributor

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;

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:

    

EmployeeIDMonthDayHoursschedule_Hours
1118184
2118184
3118184
1128
2128
3128
3138
YoussefBelloum
Esteemed Contributor

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

Community Browser