Group By Clause and Aggregate function

[mahendragaur]

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;

[Anil_Babu_Samineni]

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;

[kamal_sanguri]

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;

[Peter_Cammaert]

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.