1 Reply Latest reply: Sep 11, 2013 2:44 PM by Ernesto García RSS

    Need help with LINEST_M() syntax in script.

    Ernesto García

      Hello folks.

       

      I'm currently developing a dashboard for profiling network services. There's a very large (hundred of millions rows) log table in SQL from wich I extract and load into QlikView a smaller aggregated table with just three fields: The name of the service, the month and the average response time in miliseconds. Something like this:


      ServicePerformance:

      LOAD * INLINE
      [
      ServiceID, Month, AVGExecTime
      'Service A', 1, 2315
      'Service A', 2, 4152
      'Service A', 3, 3221
      'Service A', 4, 1572
      'Service A', 5, 1782
      'Service A', 6, 3265
      'Service B', 1, 1542
      'Service B', 2, 4351
      'Service B', 3, 1098
      'Service B', 4, 1509
      'Service B', 5, 3265
      'Service B', 6, 2517
      'Service B', 7, 2210
      'Service B', 8, 4500
      ]
      ;

       

      The problem is I need to calculate trends over time but can't figure out the correct LINEST_M(syntax). I've tried:


      Trends:

      NOCONCATENATE LOAD
      ServiceID,
      Month,
      LINEST_M(AVG(AVGExecTime), Month) as Trend
      RESIDENT ServicePerformance
      GROUP BY ServiceID, Month;

       

      But this fails with: "Error in expression:Nested aggregation not allowed"

       

      If I remove the aggregation:


      Trends:

      NOCONCATENATE LOAD
      ServiceID,
      Month,
      LINEST_M(AVGExecTime, Month) as Trend
      RESIDENT ServicePerformance

      GROUP BY ServiceID, Month;

       

      then there's no error but nothing is calculated and the field is empty.

      I need this to get calculated in the script at load time, not in graphs/tables, etc.

       

      Any help will be greatly appreciated.

       

      Regards,

      Ernesto.

        • Re: Need help with LINEST_M() syntax in script.
          Ernesto García


          Solved it. Just using the ServideID dimension for the grouping with INNER JOIN LOAD the main table did the trick:

           

          ServicePerformance:

          LOAD * INLINE
          [
          ServiceID, Month, AVGExecTime
          'Service A', 1, 2315
          'Service A', 2, 4152
          'Service A', 3, 3221
          'Service A', 4, 1572
          'Service A', 5, 1782
          'Service A', 6, 1565
          'Service B', 1, 1542
          'Service B', 2, 4351
          'Service B', 3, 1098
          'Service B', 4, 1509
          'Service B', 5, 3265
          'Service B', 6, 2517
          'Service B', 7, 2210
          'Service B', 8, 4500
          ]
          ;

          INNER JOIN LOAD
          ServiceID,
          LINEST_M(AVGExecTime, Month) as Trend
          RESIDENT ServicePerformance
          GROUP BY ServiceID;