5 Replies Latest reply: Apr 3, 2012 8:21 AM by Matthew Fryer RSS

Performing calculations during load and creating new fields

jkizanis

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???

  • Performing calculations during load and creating new fields
    Vijay Kumar

    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.

  • Performing calculations during load and creating new fields
    Matthew Fryer

    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