community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Is there a way to store table columns into variables

Highlighted

Hi, I'm interested in slimming down my formulas. Is there any OOP approach to storing column names into a variable? Something like storing [SPOUSE_AGE] into variable x and calling if-statements on that x (which would be simply calling [SPOUSE_AGE]. This would allow me to use x interchangeably for multiple fields.

 

An example of the snip-it of code I'm trying to slim down is below. I could reduce the amount of lines in this code by half if I can have a conditional if-statement to store [SPOUSE_AGE] or [SELF_AGE] into variable x, and just running x against the conditional statements rather than repeating such statements twice for both fields.

 

Thanks

1234.png

 

 

Magnetar
Magnetar

Hi,

To my knowledge the closest thing you can do to storing a column name as a variable would be to create a new column [X] as the first formula in a formula tool, and setting it as the appropriate data type, with a formula of [SPOUSE_AGE], or whatever your field would be.

 

Then you could use that field in future formulas.  You would want to use a Select tool after this point to clean up that variable by deselecting [X].

 

EDIT:

A little more specifically to your use case, a formula for field X with the following expression:

IF ISNULL([SPOUSE_AGE]) THEN [SELF_AGE] ELSE [SPOUSE_AGE] ENDIF

And then writing your IF categorization statement against column [X] instead of SPOUSE_AGE and SELF_AGE

 

Then you can deselect [X] in the next step.


This is potentially less efficient than the programmatic aliasing you reference here (I can't speak to the inner workings and optimization of the Alteryx engine in cases like this), but especially if the data is only used for this one step I doubt it would impact performance in a significant way.

Alteryx Certified Partner
Alteryx Certified Partner

Could you do something like:

 

IF 
Max([SPOUSE_AGE],[SELF_AGE]) < 30 THEN '<30'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 35 THEN '30-34'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 40 THEN '35-39'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 45 THEN '40-44'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 50 THEN '45-49'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 55 THEN '50-54'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 60 THEN '55-59'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 65 THEN '60-64'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 70 THEN '65-69'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) < 75 THEN '70-74'  ELSEIF
Max([SPOUSE_AGE],[SELF_AGE]) >= 75 THEN '75+'  
ELSE 'Error: Invalid Age Date'
ENDIF

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels