Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables and would like to make it one:
table 1:
Month | Hour | Employee |
July | 1 | A |
July | 2 | A |
July | 3 | B |
July | 4 | C |
August | 2 | A |
August | 8 | B |
August | 4 | B |
August | 1 | C |
table 2:
Month | Total |
July | 20 |
August | 18 |
What I would like to get is below. I would like to have a calculated row for each month calculated by using Total from [table 2] for the corresponding month subtracted by {sum of hours} for that month in [table 1]
Month | Hour | Employee |
July | 1 | A |
July | 2 | A |
July | 3 | B |
July | 4 | C |
July | 20-sum(1+2+3+4) = 10 | Remaining Hours |
August | 2 | A |
August | 8 | B |
August | 4 | B |
August | 1 | C |
August | 18-sum(2+8+4+1) = 3 | Remaining Hours |
Could anyone help me with the loading script?
Thanks so much!
Hi,
Please find my solution belowHope this helps
ZZ
This script will generate your result.
// First, load your data tables
[table 1]: Load * Inline [ Month, Hour, Employee July,1,A July,2,A July,3,B July,4,C August,2,A August,8,B August,4,B August,1,C ]; left Join ([table 1]) Load Month, Total Inline [ Month,Total July,20 August,18 ];
// Code to generate the table starts here [table 2]: NoConcatenate Load Month, Min(Total) - Sum(Hour) as Hour, 'Remaining Hours' as Employee Resident [table 1] Group By Month; [table 2]: Concatenate Load Month, Hour, Employee Resident [table 1]; drop Table [table 1];