Formula to pull the value from a column based on the value of another column

[rybog98]

Hello,

I have a data set with over 100 columns. In the data set I have a column called "COLUMN_NAME" that contains the name of one of the other columns. What I need to do is pull the value from the column that is specified in "COLUMN_NAME". Any ideas?

Thanks in advance

Here is an example of what I am trying to do:

COLUMN_NAME	VAR_1	VAR_2	VAR_3	Target Output
VAR_1	10	8	6	10
VAR_3	1	2	3	3
VAR_2	5	7	9	7

[Emil_Kos · Accepted solution]

Hi @rybog98,

I think this will work for you.

This is a combination of batch macro + macro from the crew macro:

Just a reminder how to get a crew macro:

You’ll need to download and install the CREW Macros first (this requires the dynamic formula tool to work).  I’ve attached the macro to the package above, but I’d recommend installing it first. Be sure to unzip the contents before you run the installation file.

Big kudos to @khess85 as she created this solution!

[patrick_digan]

@rybog98 I have an idea for an eval function which would be useful in a case like yours if Alteryx implemented it. It would then be a formula like:

Eval("["+[Column_Name]+"]")

[rybog98]

@patrick_diganThis is what I was hoping for. I think this would be very useful for many things.

[rybog98]

@Emil_KosWe ended up doing something similar. We used a batch macro to pass the formulas to a formula tool. We decided this was the best approach given our use case and data constraints. Thanks for your help!

[atcodedog05]

Hi @Emil_Kos 

I am bookmarking your post with batch macro approach 🙂

[patrick_digan]

For kicks and giggles, you can use @jdunkerley79 's alteryx abacus to store the data behind the scenes in a weird alternate universe and then use magic to bring the data back. You have to have a good understanding of Alteryx's engine to understand the VarNum functions, but they're powerful once you corral them. They're also a third party install, which is a major thing that keeps more people from being able to use it. 

[atcodedog05]

Thank you for the share @patrick_digan Something new to learn 🙂

@jdunkerley79 is definitely a great wizard of Alteryx always lot to learn 😁