Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mwscott1
Contributor

Return only when all criteria met

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 TestStatus
User ATest 1Complete
User ATest 2Complete
User ATest 3Complete
User BTest 2Registered
User CTest 1In Progress
User CTest 3Complete
7 Replies

Re: Return only when all criteria met

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

Highlighted
mwscott1
Contributor

Re: Return only when all criteria met

script, loading from .csv file.

Re: Return only when all criteria met

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
Contributor

Re: Return only when all criteria met

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 IDTraining TitleTranscript Status
User ATest 1Complete
User ATest 2Complete
User ATest 3Complete
User BTest 2Registered
User CTest 1In Progress
User CTest 3Complete

Re: Return only when all criteria met

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];

Arjunarao
Honored Contributor II

Re: Return only when all criteria met

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;



Arjunarao
Honored Contributor II

Re: Return only when all criteria met

Capture.PNG

Capture1.PNG