Group BY

[#1]

Hi,

I am trying to do group by but its giving me an error . Appreciate any help on this . Please find attached Qvw and Excel spreadsheet

Data_Temp:

LOAD Country,

     Year,

     Sales,

   if(Country = 'INDIA',Sales) as IndiaSales,

     ISSUES,

     RETURNS,

     ISSUES - RETURNS as Adjustments

FROM

GroupBY.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

     Country,

     Year,

     Sales,

     IndiaSales,

     ISSUES,

     RETURNS,

     SUM (Adjustments) as  Adjustments

     Resident Data_Temp

     Group by Country;

DROP Table Data_Temp;

EXIT Script;

[sujeetsingh]

See the sample

[Kushal_Chawda]

You are doing group on only one column whereas you have not included other columns in the group.

Use below script

Data_Temp:

LOAD Country,

     Year,

     Sales,

   if(Country = 'INDIA',Sales) as IndiaSales,

     ISSUES,

     RETURNS,

     ISSUES - RETURNS as Adjustments

FROM

GroupBY.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

     Country,

     Year,

     Sales,

     IndiaSales,

     ISSUES,

     RETURNS,

     SUM (Adjustments) as  Adjustments

     Resident Data_Temp

     Group by Country,

     Year,

     Sales,

     IndiaSales,

     ISSUES,

     RETURNS

;

DROP Table Data_Temp;

EXIT Script;

[jagan · Accepted solution]

Hi,

You need to specify all the fields in the Group by clause, if you want to use Aggregate functions in Load statement, so your query should be

Data_Temp:

LOAD Country,

     Year,

     Sales,

   if(Country = 'INDIA',Sales) as IndiaSales,

     ISSUES,

     RETURNS,

     ISSUES - RETURNS as Adjustments 

FROM

GroupBY.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

     Country,

     Year,

     Sales,

     IndiaSales,

     ISSUES,

     RETURNS,

     SUM (Adjustments) as  Adjustments

Resident Data_Temp

Group by Country,

     Year,  

     Sales,

     IndiaSales,

     ISSUES,

     RETURNS;

DROP Table Data_Temp;

OR if you want Count level adjustments then use like this

Data_Temp:

LOAD Country,

     Year,

     Sales,

   if(Country = 'INDIA',Sales) as IndiaSales,

     ISSUES,

     RETURNS,

     ISSUES - RETURNS as Adjustments 

FROM

GroupBY.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

     Country,

          SUM (Adjustments) as  Adjustments

Resident Data_Temp

Group by Country;

DROP Table Data_Temp;

Hope this helps you.

Regards,

Jagan.

[sujeetsingh]

Jagan it is a helpful one .