Compare Values From Different Rows
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the reply guys...i need to run kids to practice. Will check our you solution asap!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Wow...ok, I learned a lot from that solution. I was making this WAY more difficult.
Thanks all!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just to be clear, the selected summarize tool in the sample workflow is Crosstab, not Transpose.