topic Re: Group BY in QlikView

Group BY

Wed, 07 Aug 2013 03:52:15 GMT

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;

Re: Group BY

sujeetsingh — Wed, 07 Aug 2013 04:12:48 GMT

See the sample

Re: Group BY

Kushal_Chawda — Wed, 07 Aug 2013 04:18:28 GMT

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;

Re: Group BY

jagan — Wed, 07 Aug 2013 06:03:52 GMT

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.

Re: Group BY

sujeetsingh — Thu, 22 Aug 2013 09:17:26 GMT

Jagan it is a helpful one .