Calculating Field Value on Load

[shane_spencer]

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?

[MK_QSL]

How you want the result?

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

[Anonymous · Accepted solution]

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;

[shane_spencer]

Thanx Chris, that works a treat.