How to Count records which have same values grouping on a particular field

[fashid]

Hi Friends,

Stuck with a logic

I have a sample data which has test data for several users.I need to identify how many users have passed and how many have failed .

A user is allowed to get multiple attempts , if in any of the attempt he has passed he is considered to be pass else if he fails in all the attempts only then is he considered to be failed .

Maybe previous or peek could help but i am not sure how ?

Regards,

Nadeem

[sunny_talwar]

May be like this:

Table:

LOAD [ATTEMPT NO],

    USER,

    PASSED

FROM

[..\..\Downloads\SAMPLE DATA.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Table)

LOAD USER,

  If(MaxString(PASSED) = 'YES', 'Passed', 'Not Passed') as Flag

Resident Table

Group By USER;

[maxgro · Accepted solution]

Another option in script could be

Table:

LOAD [ATTEMPT NO],

     USER,

     PASSED

FROM

[SAMPLE DATA.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

load

  *,

  if(USER = Peek('USER'), Peek('Result'), PASSED) as Result

Resident

Table

order by USER, PASSED desc;

DROP Table Table;

in chart I think

dimension          USER

expression         count({$ <PASSED={YES}>} DISTINCT USER)

[MarcoWedel]

Hi,

one front end solution might be:

=Aggr(If(Min(PASSED='YES'),'passed',If(Min(PASSED='NO'),'failed','no attempts')),USER)

hope this helps

regards

Marco

[fashid]

Thanks a lot Sunny , Marco and Massimo . Wished i could mark all the answers as correct . Appreciate all your efforts taken to help .

[MarcoWedel]

You're welcome.

Glad it helped.

Regards

Marco