topic Re: Group By Clause and Aggregate function in QlikView

Group By Clause and Aggregate function

mahendragaur — Fri, 09 Jun 2017 06:56:00 GMT

Hello All,

I'm using below script in which I'm finding the difference between current value and previous value by using group by clause but when I'm reloading the script it throwing error as use aggregate function with group by clause. Please help me how can I do that?

Test:

LOAD ORDER_NO,
     PLANTOOL_STATUS,
     ACTION_DATE,
     MACH_PROG_NAME
FROM
[Demo\Test Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Test1:

load *,

if(ORDER_NO = peek(ORDER_NO) AND PLANTOOL_STATUS=10, ACTION_DATE - PEEK(ACTION_DATE),0) AS [Wait–Weld Start]
//sum(ACTION_DATE) AS SUM_ACTION_DATE

Resident Test

Group by ORDER_NO;

Re: Group By Clause and Aggregate function

Anil_Babu_Samineni — Fri, 09 Jun 2017 07:01:44 GMT

I don't think whether you required Group By Clause over here, Can you change it to order by like

NoConcatenate

Test1:

load *,

if(ORDER_NO = peek(ORDER_NO) AND PLANTOOL_STATUS=10, ACTION_DATE - PEEK(ACTION_DATE),0) AS [Wait–Weld Start]
//sum(ACTION_DATE) AS SUM_ACTION_DATE

Resident Test

Order by ORDER_NO ASC;

Re: Group By Clause and Aggregate function

kamal_sanguri — Fri, 09 Jun 2017 07:09:48 GMT

The Problem lies with '*'. Remove it from your script;

Test:

LOAD ORDER_NO,
    PLANTOOL_STATUS,
    ACTION_DATE,
    MACH_PROG_NAME
FROM
[Demo\Test Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Test1:

load *, //Remove this star

if(ORDER_NO = peek(ORDER_NO) AND PLANTOOL_STATUS=10, ACTION_DATE - PEEK(ACTION_DATE),0) AS [Wait–Weld Start]
//sum(ACTION_DATE) AS SUM_ACTION_DATE

Resident Test

Group by ORDER_NO;

Re: Group By Clause and Aggregate function

Peter_Cammaert — Fri, 09 Jun 2017 08:43:37 GMT

Actually, the problem is much wider than simply removing the star from your LOAD statement.

A GROUP BY clause compresses a set of rows into single rows for every value of the fields listed in the GROUP BY clause. That means that you'll have to use aggregation functions on every field NOT present in the GROUP BY clause, including ACTION_DATE and PLANTOOL_STATUS and everything else.