Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bhavvibudagam
Creator II
Creator II

simple group by condition. Please help me

Hi Experts,

can anyone please help me on this,

I have loaded the below test source file.

Test screen shot.png

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.

status screenshot.png

thanks in advance

1 Solution

Accepted Solutions
sunny_talwar

Try Only Status

or group by Status also

View solution in original post

5 Replies
sunny_talwar

Try Only Status

or group by Status also

Or
MVP
MVP

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.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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