Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Performing calculations during load and creating new fields

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

5 Replies
vijay_iitkgp
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

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

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
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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