group by

[Anonymous]

how i use group by in script i use this but this is not working

Directory;

LOAD Acccount,

     SubAccounts

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1)

group by Account;

[nagaiank]

Did you want to 'Order By' ?

[jerryyang756]

To use group by in script you need at least a aggr function (SUM,AVG,COUNT)

Directory;

LOAD Acccount,

     SubAccounts ,

     Count(SubAccounts) as NoOfSubAccounts

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1)

group by Account;

Without Aggregation function you will get an error like this

Syntax error

Aggregation expressions required by GROUP BY clause.

[alejandrow]

Hi, You only need to use the Group by Clause if you are aggregating the information .

For i.e :

Directory;

LOAD Acccount,

    COUNT(SubAccounts) as QSubAccounts

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1)

group by Account;

That wouldn't give you a error message.

If you express with some detail what you want to achieve we can help you out

[dwforest]

If you are just trying to weed out duplicates, you can use DISTINCT

Directory;

LOAD DISTINCT Acccount,

     SubAccounts

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1)

;

[sasiparupudi1]

T1:

LOAD Acccount,

     SubAccounts

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1)

T2:

Noconcatenate Load

Account,

SubAccounts

Resident T1

Group By Account;

Drop Table T1;