new Alteryx formula

[rjacobson]

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

[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 · Accepted solution]

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

[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

[rjacobson]

With my complete data set I'm now getting an error message 

iteration limit of 100 was reached

How do you open the interface designer - properties for the tool.  

I tried view/Interface designer, but nothing comes up

I believe I need this screen so that I can update the Max # of iterations but can't seem to get to it.

[CoG · Accepted solution]

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.