First two fields are easy to create by using
Hours + OTHours as OperatorTotalHours in first load from OHours table
Hours + OTHours as LaborTotalHours from LHours table.
to create other two fields you need to join these table on key value may be Laborid + Date or anything so that both values comes under single table and then create new fields.
Thank you. I had tried the '+' operator during load and I thought I had it wrong because the results were not correct.
If OperatorRegHours is not null and OperatorOTHours is null, the result OperatorTotalHours shows as null. I am attaching a test file. Is it possible to fix this?
COST CALCULATION - TEST.qvw 176.2 K
try to use rangesum() function instead of the + operator. Rangesum will take care of your NULL's
rangesum(Hours,OTHours) as TotalHours
As Vijay said, you might consider using a join or concatenate to create one table instead of two, maybe this blog is of interest for you:
If you concatenate the tables, you might also consider creating a flag with values 'Operator' and 'Labor' instead of separate fields for the hours, but that's up to you.
You need to join the tables together into a temp table first and then you can load from this temp table into a final version and perform your calculations. Like this:
Hours as OperatorRegHours,
OTHours as OperatorOTHours,
rangesum(Hours, OTHours) as OperatorTotalHours
from OHours.xls (biff, embedded labels, table is [Sheet1$]);
OUTER JOIN (TempTable)
Hours as LaborRegHours,
OTHours as LaborOTHours,
rangesum(Hours, OTHours) as LabourTotalHours
from LHours.xls (biff, embedded labels, table is [Sheet1$]);
Month(Date) as Month,
Year(Date) as Year,
Day(Date) as Day,
rangesum(OperatorTotalHours, LabourTotalHours) as TotalHours
DROP TABLE TempTable;