3 Replies Latest reply: Feb 23, 2015 3:48 AM by Shane Spencer RSS

    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?

        • Re: Calculating Field Value on Load
          Manish Kachhia

          How you want the result?

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

          • Re: Calculating Field Value on Load
            Chris Deniziak

            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

            [C:\Users\data\Downloads\LinuxMetrics_CPU.qvd]

            (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;