Return only when all criteria met

[mwscott1]

I have a data set and I only want to return users who have completed all 3 test (status Complete). In the example below only User A would be returned having completed all 3 test.

User	Test	Status
User A	Test 1	Complete
User A	Test 2	Complete
User A	Test 3	Complete
User B	Test 2	Registered
User C	Test 1	In Progress
User C	Test 3	Complete

[sunny_talwar]

You want this to be filtered in the script or front end chart?

[mwscott1]

script, loading from .csv file.

[sunny_talwar]

May be this

Table:

LOAD User,

     Test,

     Status

FROM .....;

Right Join (Table)

LOAD User

Where Count = 3;

LOAD User,

     Count(DISTINCT Test) as Count

Resident Table

Group By User;

[mwscott1]

I'm sorry I forgot about the status also has to be complete. This is my load script so far. How would I return only the users who have all 3 Training Title (Test 1, Test 2, Test 3) and all 3 have Transcript Status (Complete)

PS_4CBT:

LOAD [User ID],

     [Training Title],

     [Transcript Status]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

User ID	Training Title	Transcript Status
User A	Test 1	Complete
User A	Test 2	Complete
User A	Test 3	Complete
User B	Test 2	Registered
User C	Test 1	In Progress
User C	Test 3	Complete

[sunny_talwar]

May be this

PS_4CBT:

LOAD [User ID],

     [Training Title],

     [Transcript Status]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Right Join (PS_4CBT)

LOAD [User ID]

Where Count = 3;

LOAD [User ID],

     Count(DISTINCT [Training Title]) as Count

Resident Table

Where [Transcript Status] = 'Complete'

Group By [User ID];

[qlikviewwizard]

Hi,

Try like this.

DATA:

load * inline [

User,Test Type,Status

User A,Test 1,Complete

User A,Test 2,Complete

User A,Test 3,Complete

User B,Test 2,Registered

User C,Test 1,In Progress

User C,Test 3,Complete

User D,Test 1,Complete

User D,Test 2,Complete

User D,Test 3,Complete

User E,Test 1,Complete

User E,Test 2,Complete

User E,Test 3,Complete

];

JOIN

DATA_Final:

LOAD * where Count=3;

LOAD User,count(Status) as Count,User as [User Completed all 3 Tests] Resident DATA where Status='Complete' group by User;

[qlikviewwizard]