Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
How you want the result?
Group by Date and Server or Group by Date, Server and Hour ALL?
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;
Thanx Chris, that works a treat.