Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a legacy database which was created from a spreadsheet which became slow and difficult to update due to the amount of formulas within.
Overtime can be split over two rates ie. 6 hours @ 1.5(time and half) then 2 Hours at 2(Doubletime) or 3(Tripletime)
Database is structured as thus
EmployeeID,
OvertimeDate,
OvertimeWorked1,
OvertimeRate1,
OvertimeWorked2,
OvertimeRate2,
so OvertimeWorked1 and OvertimeRate1 may or may not have an entry and if so could be 1.5,2 or 3
OvertimeWorked2 and OvertimeRate2 may or may not have an entry and if so could be 1.5,2 or 3
Just wondering if I can chart the total hours worked at rates 1.5, 2 and 3
example table
Any help or advice greatly appreciated
EmployeeID | OvertimeDate | OvertimeWorked1 | OvertimeRate1 | OvertimeWorked2 | OvertimeRate2 |
---|---|---|---|---|---|
1 | 20-06-2016 | 0 | 0 | 5 | 1.5 |
2 | 21-06-2016 | 5 | 1.5 | 0 | 0 |
3 | 21-06-2016 | 5 | 2 | 2 | 3 |
4 | 22-06-2016 | 5 | 1.5 | 3 | 2 |
Maybe you can load your table like
LOAD EmployeeID,
OvertimeDate,
OvertimeWorked1 as OvertimeWorked,
OvertimeRate1 as OvertimeRate,
1 as Type
FROM ...;
CONCATENATE
LOAD EmployeeID,
OvertimeDate,
OvertimeWorked2 as OvertimeWorked,
OvertimeRate2 as OvertimeRate,
2 as Type
FROM ...;
Then you can use OvertimeRate as dimension and
=Sum(OvertimeWorked)
as expression.
Maybe you can load your table like
LOAD EmployeeID,
OvertimeDate,
OvertimeWorked1 as OvertimeWorked,
OvertimeRate1 as OvertimeRate,
1 as Type
FROM ...;
CONCATENATE
LOAD EmployeeID,
OvertimeDate,
OvertimeWorked2 as OvertimeWorked,
OvertimeRate2 as OvertimeRate,
2 as Type
FROM ...;
Then you can use OvertimeRate as dimension and
=Sum(OvertimeWorked)
as expression.
Thanks very much
Just a question
What does the 1 as type statement do or achieve
I created an additional field 'Type' to be able to distinguish between the original two rates / OverTimeWorked fields.
Just in case you need to know what the original fields were named like.
Thanks
So it is just a descriptive field and I could remove it if I feel the need to.
Many thanks again for the advice.