Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try Only Status
or group by Status also
You can't average string values, I think. You'll have to pick an aggregation function that can handle strings.
Sum, Avg works only for integer fields not the string fields. So, in your case Status is the string.
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;
Sample attached