Alteryx Designer Discussions

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

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

HSuffolk
7 - Meteor

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

9 REPLIES 9
binay2448
11 - Bolide

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

HSuffolk
7 - Meteor

HSuffolk_0-1633344911149.png

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
11 - Bolide

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
11 - Bolide

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
7 - Meteor

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
11 - Bolide

@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
11 - Bolide

Please find attached solution, it may help...

danilang
18 - Pollux
18 - Pollux

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
7 - Meteor

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

Labels