Looking up Values Multiple Columns to create a table

Hi guys!

I've been using Alteryx for about a year now and have always found good advice on the forums. I'm running across a unique problem here and I haven't been able to find any guidance.

To start with, here is a sample of the data I am working with:

ID	Replaced By	Date
26750	31981	6/28/2010
27748	31981	6/28/2010
31981	31956, 29813	5/11/2011
29813	43797	1/14/2016
31956	43852	1/14/2016
43797		7/1/2019
43852		7/1/2019

So I am trying to isolate the newest two IDs (last two rows) and create a list of IDs that have preceded each of them. The desired output would be something like this:

ID	GroupID
26750	ABC1
27748	ABC1
31981	ABC1
29813	ABC1
43797	ABC1
26750	ABC2
27748	ABC2
31981	ABC2
31956	ABC2
43852	ABC2

I feel like I should be doing some sort of macro that looks at the values in the 'Replaced By' column, scans the ID column, finds a match, then pulls the value from the match's Replaced by column, scans the ID column, finds a match, etc... and stops once it finds a match whose replaced by column is null...

Does anyone have any ideas for me, or is there anything I can clarify?

Thanks a lot!

Macros

Dynamic Processing

Iterative Macro

Accepted answers

benakesh

Hi @Shafeek ,

Attached revised versions of main wf and macros . The wf runs for about 50 seconds.

itr3.yxmc

bat3.yxmc

call_bat3_itr3.1.yxmd

All comments

ganesamurthy

Hi @Shafeek

This is a perfect case for combining all three type of macros!! I am attaching my workflow that works perfectly for your requirement below.

Main workflow:

Spoiler

Innermost Standard Macro:

Spoiler

Next outer level Iterative Macro:

Spoiler

Next outer level Batch Macro:

Spoiler

Let me know if you have any queries. Thanks.

Looking up Values Multiple Columns to create a table.yxzp

benakesh

Hi @Shafeek ,

This is the version with batch and iterative macros .

Batch calls iterative macro for each group .

Iterative macro loops through for all previous ids

call_bat3_itr3.yxmd

bat3.yxmc

itr3.yxmc

Shafeek

Hi @benakesh - This is very close to what we're trying to accomplish and I just have to throw in an extra wrinkle that I did not include in my original post... sorry about that!

So right now your macros are sorting the IDs in an ascending order and then scanning the columns. The logic makes sense, but the output needs to be modified in a small way. For each of IDs we have a 'temp ID' that groups the IDs together in a family of IDs. For example, here is one grouping:

ID	Replaced By	Temp ID	Date
26750	31981	9	6/28/2010
27748	31981	9	6/28/2010
31981	31956, 29813	9	5/11/2011
29813	43797	9	1/14/2016
31956	43852	9	1/14/2016
43797		9	7/1/2019
43852		9	7/1/2019

and another grouping:

ID	Replaced By	Temp ID	Date
31036	27278	9734	6/28/2010
27278	29813	9734	5/11/2011

The way this affects your macros is that is scanning across both groups and creating the history while ignoring the Temp ID grouping. So ID 43797 ends up with a list that looks like this:

31036

27278

29813

43797

When it should look like this:

26750

27748

31981

29813

43797

Again, I'm sorry for having omitted this piece of information. Does my explanation make sense?

@ganesamurthy - I have not tried your macro out yet, but I can imagine it will run into a similar issue since... again... I did not mention this situation in my original post.

I hope my explanation helps clarify the last hurdle. It really is amazing work what you've done and I've given both of your posts likes... if you could modify your workflows to accommodate this Temp ID it would be really helpful!

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703