Compare Values From Different Rows

rdugger

I'm a new Alteryx user...trying to solve a problem:

Given the input data below, i need to output one row per User based on the following rules:

1. if Course Name = Cert Exam then 1 else 0
2. if Course Name = Sales Exam - New and Product Exam - New then 1 else 0

* Note for user4 below, they completed both the old and new exams, but only need to be counted once

Input:

User	Course Name
user1@myco.com	Sales Exam - New
user2@myco.com	Cert Exam - Old
user3@myco.com	Cert Exam - Old
user4@myco.com	Product Exam - New
user4@myco.com	Cert Exam - Old
user4@myco.com	Sales Exam - New
user5@myco.com	Product Exam - New
user6@myco.com	Cert Exam - Old
user7@myco.com	Product Exam - New
user7@myco.com	Sales Exam - New
user8@myco.com	Sales Exam - New
user9@myco.com	Sales Exam - New
user9@myco.com	Product Exam - New
user10@myco.com	Sales Exam - New
user10@myco.com	Product Exam - New
user11@myco.com	Sales Exam - New
user11@myco.com	Product Exam - New

Desired Output:

User	Cert
user1@myco.com	0
user2@myco.com	1
user3@myco.com	0
user4@myco.com	1
user5@myco.com	1
user6@myco.com	1
user7@myco.com	1
user8@myco.com	0
user9@myco.com	1
user10@myco.com	1
user11@myco.com	1

Any input to solve this is much appreciated!

sample.xlsx

JoeM

@rdugger

Solution is attached. 

1) I first transpose my data so all the couses a user takes can be found in a single field

2) The using conditional logic, I built the same logic you stated above

3) Removed the courses field using a select

Also, user3 is supposed to intended to be 1 based on this logic, correct?

Sample.yxzp