Performing calculations during load and creating new fields

[#1]

I have two loads in  my script

load

                              Date as                               Date,

                              Month(Date) as                     Month,

                              Year(Date)  as                     Year,

  Day(Date)   as Day,

   Hours as                               OperatorRegHours,

   OTHours as OperatorOTHours

from OHours.xls (biff, embedded labels, table is [Sheet1$]);

load

  Date as Date,

  Month(Date) as Month,

  Year(Date)  as Year,

  Day(Date)   as Day,

   Hours as LaborRegHours,

   OTHours as LaborOTHours

from LHours.xls (biff, embedded labels, table is [Sheet1$]);

Ideally, I would like to have a field called OperatorTotalHours as (OperatorRegHours+OperatorOTHours), a field called LaborTotalHours as (LaborRegHours+LaborOTHours) and a field called TotalHours as  (OperatorTotalHours +LaborTotalHours).

Is it possible to achieve this during load???

[vijay_iitkgp]

Hi,

First two fields are easy to create by using

Hours + OTHours as OperatorTotalHours in first load from OHours table

and

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.

[#3]

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?

[swuehl · Accepted solution]

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:

http://qlikviewnotes.blogspot.de/2009/11/understanding-join-and-concatenate.html

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.

[vijay_iitkgp]

Hi Stephen,

I think Concatenation will not work here because it will create two different rows for OHours and LHours and sum will not give desired result. Otherwise he has to do aggregation at key level

[#6]

Hi

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:

TempTable:

load

    Date,

    Hours as OperatorRegHours,

    OTHours as OperatorOTHours,

    rangesum(Hours, OTHours) as OperatorTotalHours

from OHours.xls (biff, embedded labels, table is [Sheet1$]);

OUTER JOIN (TempTable)

LOAD

    Date,    

    Hours as LaborRegHours,

    OTHours as LaborOTHours,

    rangesum(Hours, OTHours) as LabourTotalHours

from LHours.xls (biff, embedded labels, table is [Sheet1$]);

FinalTable:

LOAD

     *, 

    Month(Date) as Month,

    Year(Date)  as Year, 

    Day(Date)  as Day,

    rangesum(OperatorTotalHours, LabourTotalHours) as TotalHours

RESIDENT TempTable;

DROP TABLE TempTable;

Regards

Matt