Need help with LINEST_M() syntax in script.

consenit — Wed, 11 Sep 2013 18:09:26 GMT

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.

consenit — Wed, 11 Sep 2013 18:44:25 GMT

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;

topic Need help with LINEST_M() syntax in script. in QlikView

Need help with LINEST_M() syntax in script.

Re: Need help with LINEST_M() syntax in script.