topic Re: Calculating Field Value on Load in QlikView

Calculating Field Value on Load

shane_spencer — Fri, 20 Feb 2015 15:56:35 GMT

I've got a QVD with the following fields:

What I need to do is create a new "sub_Metric" called "% Processor Time" based on the following calculation:

100 - (value of %iowait + value of %idle)

This is easy to calculate in the front end using set analysis but I need to do created a new field in a load (and export to qvd) in the same format as above. Is this possible?

Re: Calculating Field Value on Load

MK_QSL — Fri, 20 Feb 2015 16:12:58 GMT

How you want the result?

Group by Date and Server or Group by Date, Server and Hour ALL?

Re: Calculating Field Value on Load

Anonymous — Fri, 20 Feb 2015 16:32:58 GMT

I'm guessing you want to concatenate it to your existing data set... Would look something like this:

Data:

LOAD Date,

Hour,

Minute,

Server,

Main_metric,

sub_Metric,

value

FROM

(qvd);

_temp_Data:

LOAD

Date,

Hour,

Minute,

Server,

Main_metric,

sum(if(sub_Metric = '%iowait', value)) as %iowait,

sum(if(sub_Metric = '%idle', value)) as %idle

Resident Data

Group By Date,

Hour,

Minute,

Server,

Main_metric;

Concatenate(Data)

LOAD

Date,

Hour,

Minute,

Server,

Main_metric,

'% Processor Time' as sub_Metric,

(100 - sum(%iowait + %idle)) as value

Resident _temp_Data

Group By Date,

Hour,

Minute,

Server,

Main_metric;

DROP Table _temp_Data;

Re: Calculating Field Value on Load

shane_spencer — Mon, 23 Feb 2015 08:48:50 GMT

Thanx Chris, that works a treat.