Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Looking up Values Multiple Columns to create a table

Shafeek
5 - Atom

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:

 

IDReplaced ByDate
26750319816/28/2010
27748319816/28/2010
3198131956, 298135/11/2011
29813437971/14/2016
31956438521/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:

 

IDGroupID
26750ABC1
27748ABC1
31981ABC1
29813ABC1
43797ABC1
26750ABC2
27748ABC2
31981ABC2
31956ABC2
43852ABC2

 

 

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!

 

 

7 REPLIES 7
ganesamurthy
8 - Asteroid

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
ganesamurthy_0-1572689030337.png

Innermost Standard Macro:

Spoiler
ganesamurthy_1-1572689090374.png

Next outer level Iterative Macro:

Spoiler
ganesamurthy_2-1572689132207.png

Next outer level Batch Macro:

Spoiler
ganesamurthy_3-1572689208684.png

 

Let me know if you have any queries. Thanks.

benakesh
12 - Quasar

Hi @Shafeek ,

This  is  the  version  with  batch and iterative macros . 

Batch  calls iterative macro for each group . 

benakesh_0-1572707862043.png

 

Iterative macro  loops through for all previous ids 

benakesh_1-1572708014417.png

Shafeek
5 - Atom

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:

 

IDReplaced ByTemp IDDate
267503198196/28/2010
277483198196/28/2010
3198131956, 2981395/11/2011
298134379791/14/2016
319564385291/14/2016
43797 97/1/2019
43852 97/1/2019

 

and another grouping:

 

IDReplaced ByTemp IDDate
310362727897346/28/2010
272782981397345/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
12 - Quasar

Hi @Shafeek ,

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

benakesh_0-1572897005193.png

 

Shafeek
5 - Atom

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
12 - Quasar

Hi @Shafeek ,

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

Shafeek
5 - Atom

It worked! Thank you so much for your help! 

Labels