Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare Values From Different Rows

rdugger
5 - Atom

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!

7 REPLIES 7
JoeM
Alteryx Alumni (Retired)

@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
11 - Bolide

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
Alteryx Alumni (Retired)

@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
5 - Atom

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

JoeM
Alteryx Alumni (Retired)

@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
5 - Atom

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

 

Thanks all!

BigHeadFred
5 - Atom

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

Labels