Looking up Values Multiple Columns to create a table

[Shafeek]

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!

[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.

[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 

[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:

ID

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!

[benakesh]

Hi @Shafeek ,

Revised wf  change :  Adding  'temp ID'   to  group. 

[Shafeek]

Hi @benakesh, I tried to plug in the full list that I have and it keeps getting stuck at 69% in the macro - I tried running it for 30 minutes and it never budged. It seems to be endlessly looping.

Any chance you could try it on your end and see if it completes all the way through?

[benakesh · Accepted solution]

Hi @Shafeek ,

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

[Shafeek]

It worked! Thank you so much for your help!