topic Performing calculations during load and creating new fields in QlikView

Performing calculations during load and creating new fields

Tue, 03 Apr 2012 09:01:37 GMT

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_iitkgp — Tue, 03 Apr 2012 09:27:00 GMT

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.

Re: Performing calculations during load and creating new fields

Tue, 03 Apr 2012 10:35:28 GMT

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?

Performing calculations during load and creating new fields

swuehl — Tue, 03 Apr 2012 11:37:18 GMT

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.

Performing calculations during load and creating new fields

vijay_iitkgp — Tue, 03 Apr 2012 11:52:35 GMT

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

Performing calculations during load and creating new fields

Tue, 03 Apr 2012 12:21:44 GMT

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