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

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

fashid — Mon, 30 Jan 2017 16:26:16 GMT

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

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

sunny_talwar — Mon, 30 Jan 2017 16:31:25 GMT

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;

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

maxgro — Mon, 30 Jan 2017 17:22:22 GMT

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)

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

MarcoWedel — Tue, 31 Jan 2017 00:35:09 GMT

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

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

fashid — Tue, 07 Feb 2017 04:50:07 GMT

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

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

MarcoWedel — Tue, 07 Feb 2017 06:51:28 GMT

You're welcome.

Glad it helped.

Regards

Marco