Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted

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

Highlighted
Creator
Creator

script, loading from .csv file.

Highlighted

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;

Highlighted
Creator
Creator

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
Highlighted

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

Highlighted
Master II
Master II

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;



Highlighted
Master II
Master II

Capture.PNG

Capture1.PNG