Newbie - advice on totaling hours worked from overtime database

[spikenaylor1]

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

[swuehl · Accepted solution]

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.

[spikenaylor1]

Thanks very much

Just a question

What does the 1 as type statement do or achieve

[swuehl]

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.

[spikenaylor1]

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.