simple group by condition. Please help me

[bhavvibudagam]

Hi Experts,

can anyone please help me on this,

I have loaded the below test source file.

I have tried to the avg(status) and avg(score) by staff id to avoid the duplicate staff ids. like below

Test:

LOAD [Staff ID],

     [Investment name],

     Status,

     Score

FROM

(ooxml, embedded labels, table is Sheet1);

Groupby:

load

[Staff ID],

avg(Score)as subsectionscore,

avg(Status) as subsectionstatus

Resident Test

group by [Staff ID];

drop table Test;

but getting the output like below. Under subsectionstatus i need to get Red,Green.

thanks in advance

[sunny_talwar · Accepted solution]

Try Only Status

or group by Status also

[Or]

You can't average string values, I think. You'll have to pick an aggregation function that can handle strings.

[Anil_Babu_Samineni]

Sum, Avg works only for integer fields not the string fields. So, in your case Status is the string.

[sunny_talwar]

Option1

Test:

LOAD [Staff ID],

    [Investment name],

    Status,

    Score

FROM

(ooxml, embedded labels, table is Sheet1);

Groupby:

LOAD [Staff ID],

    Avg(Score)as subsectionscore,

    Only(Status) as subsectionstatus

Resident Test

Group by [Staff ID];

DROP Table Test;

Option2

Test:

LOAD [Staff ID],

    [Investment name],

    Status,

    Score

FROM

(ooxml, embedded labels, table is Sheet1);

Groupby:

LOAD [Staff ID],

     Status as subsectionstatus,

    Avg(Score)as subsectionscore

Resident Test

Group by [Staff ID], Status;

DROP Table Test;

[sunny_talwar]

Sample attached