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!

[JoeM · Accepted solution]

@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?

[DataBlender]

Hi Joe,

You beat me to it - my solution was the same except rather than transpose I just used Summarize -> Group by user, then concatenate course name. This avoids having to create a dummy field first. 

Is there a particular reason why you've used the transpose method? (e.g. particular problems that can be avoided)

DB

[RodL]

@rdugger,

Since you are new to Alteryx, I thought I would take what @JoeM provided and what @DataBlender suggested along with another less "elegant" workflow to show you that there is not really a "right" answer (it's more about does it get you what you need). As you can see, there are a number of ways to get to what you need.

As you approach a data problem like your question, I like to suggest that you think about the steps your mind would take to solve the problem and then just "draw it out" in Alteryx. Your mind may approach the problem a little differently than someone else, but with Alteryx it really doesn't matter.

Hope this helps with letting you "think outside the box" in how to approach data problems with Alteryx.

[rdugger]

Thanks for the reply guys...i need to run kids to practice.  Will check our you solution asap!

[JoeM]

@DataBlender

Problems to be avoided using one solution over another? Not really, we are just experiencing the artistry of Alteryx allowing people to build workflows the way they think!

How I was thinking for this solution:

I typically opt to concat with a crosstab for more control over field size. My solution's concat result came out as a v_string with a length of 2048 (default but configurable), while your solution probably was a v_string with a length of 2147483647 (non-configurable). I don't expect the performance to be any different since it's a v_string, but I personally like keeping things tight.  Also, the crosstab method can have the upside of specifying what character to use to seperate the fields which adds the the flexibily of the tool.

[rdugger]

Wow...ok, I learned a lot from that solution.  I was making this WAY more difficult.

Thanks all!

[BigHeadFred]

Just to be clear, the selected summarize tool in the sample workflow is Crosstab, not Transpose.