Referencing a value in a cell and using that as the field name within a formula

[HSuffolk]

Hi all

I'm looking to reference a value in a cell when referencing the name of a field whilst in a formula. An example would be something simple like:

IF [Field] = 0 THEN "Yes" ELSE "No" ENDIF

Where, instead of Field, you'd reference a value instead that held the name of the Field. The purpose of this would be to bring through the values of different fields under certain conditions into a new field, the names of these fields in this instance are named in one column.

Hope that makes sense - please ask if I need to be more clear.

Thanks

H

[binay2448]

Share some sample data with output, it will help to understand your problem.

[HSuffolk]

Here is an example. The first field holds the name of the field which I want to bring through into the output field. Does that make sense?

Thanks

H

[Christina_H]

Sample data would help, but it sounds like you need to put your formula tool in a macro to let you dynamically change the field used.  The Dynamic Formula tool from CReW macros should do it if you have that.

[Christina_H]

My response on this thread covers something very similar:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Make-a-tool-dynamic-from-the-datastrea...

In your case you need to be updating the field referenced in a formula tool rather than the regex statement, but it's exactly the same principle.

[HSuffolk]

Thanks for the responses.

Unfortunately I'm running an older version of Alteryx, and I can't open the package you uploaded in that thread. I also can't download the CReW macros as they are unable to run on Alteryx Server, which is my end-goal.

On that thread - the task looks to be using RegEx to change the pattern, whereas the [Field 1] element is static. Is this right? Do you have an example of how to change the field dynamically using RegEx or similar?

Apologies if I'm not making too much sense - new to a lot of this!

Thanks

H

[Christina_H]

@HSuffolk Have you tried opening the workflow from my package directly?  I often get that same error and Alteryx refuses to open it straight away, but if you find it in your downloads and try again it will work, just dismiss the warning message about Alteryx versions.

You're right that in the previous example we were updating the RegEx pattern, but change the tool to a formula and you can update the action tool to change the referenced field instead.

[binay2448 · Accepted solution]

Please find attached solution, it may help...

[danilang]

Hi @HSuffolk 

A few points here.

1.  To open a newer version of a workflow or package, see the instructions here.

2.  The CReW macros are not supported by Alteryx when used on Server, but they do work correctly on Server.  That means that if you run into a problem running a workflow on Server, Alteryx won't help you through the official support channels.  You'll still be able to get help help in the community.  The CReW Macros were originally developed as a side project by Alteryx's own Adam Riley.  Support and continued development was passed on to Chaos Reigns Within(CReW).  The CReW macros are very stable and even though not officially supported, they're widely used on both Desktop and Server environments.  The macros are installed and used on all of the servers at my company. 

Dan    

[HSuffolk]

Thanks for the responses everybody, lots learned!

In the end, the solution wasn't to make the formula dynamic, it was to transpose and match the field names as suggested by binay2448. The solution explained using CReW looks to have been a solution too - I'll see if I can download them now.

Thanks

H