new Alteryx formula

I'm wondering if someone can help me with a formula/tool to look at column ID and Acct below and create the 4th (Desired output) column? I have about 30K+ rows of data with similar information in column 2 and 3. The Desired output doesn't necessarily need to count up but needs to be unique for each id/acct group.

As you can see Desired output = 1 is all matched because row 1 and 2 has the same Acct and therefore all IDs = 57559 and 57652 are grouped.

Similar, desired output 4 is grouped because acct 148996 linked to 57570 and 57742 and 180173 links 57570 and 57706

Thanks for any help

Rich

Row	ID	Acct	Desired output:
1	57559	5234	1
2	57652	5234	1
3	57559	106788	1
4	57559	124003	1
5	57559	128661	1
6	57652	128661	1
7	57559	304506	1
8	57559	305162	1
9	55123	12345	2
10	55124	12345	2
11	55126	22345	3
12	57570	145365	4
13	57570	148996	4
14	57742	148996	4
15	57570	149016	4
16	57570	149024	4
17	57570	153963	4
18	57570	180156	4
19	57570	180173	4
20	57706	180173	4

Academy

Accepted answers

griffinwelsh

@rjacobson have a look at the attached solution. There is probably a simpler way to do this but this works.

Solution.yxzp

CoG

You need to open the macro workflow (.yxmc) by right clicking the macro tool and selecting "Open Macro", and there you will find that menu in View -> Interface Designer -> Settings (Gear Icon).

Make the change and make sure to save the macro again before returning to the original workflow and running. A window should pop up saying that the macro has been updated.

All comments

shancmiralles

hi! can you elaborate a bit more please ? thanks!

rjacobson

I'm trying to group IDs and Accts together if there is any equal values between rows. For group 4, it's like an umbrella company that has 3 companies (IDs) and seven employees (Acct). I need to use the information within ID and Acct to create that group = 4(Desired output:).

Does that help?

griffinwelsh

@rjacobson have a look at the attached solution. There is probably a simpler way to do this but this works.

Solution.yxzp

rjacobson

WOW, that is cool! and thank you soo much. I have an older version of Alteryx so I don't think I have batch or whatever that tool is before the join, but this seems to work well.

I modified it lightly changing the second summary tool to be Max of the RecordID and Joined that back to the original dataset by acct which worked as well.

Again, thanks. I've been thinking about how to do this for a few days.

Rich

griffinwelsh

@rjacobson glad this worked for you. That tool is an iterative macro that I built for this usecase. I believe you should have access to these. If you want to learn more about iterative macros check out the resources below:

https://help.alteryx.com/current/en/designer/apps-and-macros/macros/iterative-macro.html

https://www.youtube.com/watch?v=AByfTPqrUrQ

CoG

You need to open the macro workflow (.yxmc) by right clicking the macro tool and selecting "Open Macro", and there you will find that menu in View -> Interface Designer -> Settings (Gear Icon).

Make the change and make sure to save the macro again before returning to the original workflow and running. A window should pop up saying that the macro has been updated.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

hhaigh1 280