Alteryx Designer Ideas

Share your Designer product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Equivalent to Excel Indirect function to dynamically change values in a formula

In Excel you can use an Indirect function to create strings to return a valid cell reference.

 

For example if in column A you have a list of sheet names e.g.

 

A1 = New York

A2 = Paris

A3 = London

 

Then you could create a formula which is Indirect(A1&"!B2") to reference the cell B2 in the New York tab.

 

An example of where I'd want to use this in Alteryx is to create a dynamic value based on a variable field.

 

For example based on this data:

cgoodman3_0-1623400053993.png

 

I write [car make]+"_"+[car model] in the formula it will return the following

Volkswagen_Golf

Pontiac_Firebird

Audi_TT

Subaru_Outback etc.

 

However if I want it to be dynamic in an app or macro to allow the user to choose a suffix via a drop down this is not straight forward.

If I set up the tooling in this way:

cgoodman3_1-1623400262180.png

 

What I get is the [#1] will bring in the field name and not the field value. So it would come through as Volkswagen_car model.

 

So my suggestion is that you have the ability to something like

[car make]+indirect("["+[#1]+"]") which would evaluate to [car make]+"_"+[car model] when car model is selected in the drop down.

 

The current workaround with help from @JonathanSherman is to use a dynamic select where [Name] = [#1] then join that onto the original dataset and use a dynamic rename create a new dynamic suffix field that can then be used.

 

cgoodman3_2-1623400697588.png

 

Which while a nice design pattern, isn't as intuitive as indirect for those coming from a strong Excel background.

 

 

1 Comment
cgoodman3
12 - Quasar
12 - Quasar

Having thought about it, potentially over complicating it due to the original way of wiring in the drop down. Using an action tool does allow this to change dynamically without the need for the dynamic select, join, dynamic rename.

cgoodman3_1-1623402477742.png

 

 

cgoodman3_0-1623402455712.png

 

However when wired in this configuration the interface pane cannot be used for debugging.

cgoodman3_2-1623402571467.png