Automation

[SuzieR]

I have a manual process that pulls data from a tab delimited report produced by our system, where we modify it in Excel.  The report pulls the primary field, and its parent field for every primary field at all levels. The manual process is to use vlookup to find the grandparent by looking up the parent in the primary column and returning its parent. The process is repeated to get great grandparent and on for a total of 13 generations. (Formulas are =VLOOKUP(B2,$A:$B,2,FALSE) in column C through  =VLOOKUP(M2,$A:$B,2,FALSE) in column N for what is currently 23,728 rows). This is just one part of the process, but one thing at a time. Needless to say, this spreadsheet is a monster to update on a weekly basis!

My task is to try and automate this process as much as possible without making it too complicated for others with no coding experience to use and maintain.  I have created a SQL database where the results of this part of the process as well as the others can be loaded, and I can write some simple caned SQL queries for anyone to run.

Can anyone think of a way to recreate the lineage process in Alteryx that will not require utilizing external code so that a beginner Alteryx user will be able to understand and maintain?

Thanks!

[Ben_H]

Hi @SuzieR ,

I'm sure we could do something with this.

Do you have some sample data you can share? - dummy data would be fine.

Regards,

Ben

[MattBSlalom · Accepted solution]

I've built out similar scenarios before.  Since you have explicitly called out 13 generations and the need for beginner Alteryx users to understand the workflow.  I might lean toward just adding 13 Join tools one after the next with "L" input being the results of the previous Join and the "R" input being the source data each time.

A cleaner looking workflow that's a bit more advanced would be to build an iterative macro that loops 13 times continually adding the next set of fields onto the result set.

As mentioned, a dummy dataset would be helpful to illustrate with if needed.

[SuzieR]

I went with the multiple joins in my workflow.  Since inexperienced users may need to use this process, having all the work being done visually will work best.  It actually looks pretty cool. 

Thanks!