alteryx Community

General Discussions

Discuss any topics that are not product-specific here.
SOLVED

new Alteryx formula

6 - Meteoroid

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
7 REPLIES 7
10 - Fireball

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

6 - Meteoroid

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?

12 - Quasar

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

6 - Meteoroid

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

12 - Quasar

@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

6 - Meteoroid

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.

13 - Pulsar

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.

Labels