Looping with unknown , unsure??

michael_franz

Hello,

So i'm new to Alteryx, so I hope I'm creating an apporpriate subject.... I am connected to a sql database. For each customer, we have service teams. Service teams can either be sales person or rep person. There must be atleast 1 of each, but could be 2 or more but the number on the customer is unknown. 95% of the data only has 1 sales or rep, but I have to count for the others

Data Looks like the: (the 2 tables have a UID)

T_Customer

Customer Name      Customer #

Michael Jackson          12345

T_People

Employee           Type             Primary       

Steve                  Sales                Y

Jane                   Sales                N

Amy                    Rep                  Y

Michelle              Rep                  N

Suz                     Rep                  N

I think I need data like this:

Customer Name      Customer #      Primary Sales         Primary Rep        Second Sales       Second Rep      Third Rep

Michael Jackson          12345           Steve                         Amy                        Jane                     Michelle          Suz

But again, not sure how many there are.....

I will also have revenue transactions by customer. My problem with the row format is I got the revenue for each person. Any help appreciated

KaneG

Hi Michael,

Firstly, one of the great things about Alteryx is that you can do all your Analysis/Grouping/Organising in one format and then easily turn your data into another format. I say that, because it is going to be a lot easier to work with in a transposed format as there will be an indeterminate number of Sales & Reps for each person...

So, starting with this data:

Customer Name Customer #
Michael Jackson 12345
Tom Jones 23456
DeeDee Ramone 34567

Employee Type Primary CustomerID
Amy Rep Y 12345
Tom Sales Y 12345
Cindy Sales N 12345
Michelle Rep N 12345
Suz Rep N 12345
Bob Rep Y 23456
Tom Sales Y 23456
Jane Sales N 23456
Linda Rep N 23456
Steve Sales Y 34567
Jane Sales N 34567
Linda Rep Y 34567
Michelle Rep N 34567
Suz Rep N 34567
Bob Rep N 34567

- Join the data together on CustomerID (Or UID) and then

- Sort by ID, Type, Primary (Desc) followed by a

- Multi-Row Formula grouped on ID & Type with the formula: [Row-1:Rank]+1 to make a field named Rank

- Formula tool with a switch function to create RankText: 

Switch([Rank],[Rank],
1,"Primary",
2,"Second",
3,"Third",
4,"Fourth")

- Another Formula to create Header: [RankText] + " " + [Type]

- Crosstab to get table

- Select to re-order fields

This way, if you have revenue per person, you can attach it before the crosstab. The main thing that this does not take into account is the order of non-primary Sales/Reps.

I have attached a module for this example.

Kane

Customer Join with Sales Reps - 2908.yxmd