Relative field references

Hi All,

I have a question about relative field references. Not sure if this is possible in Alteryx.

Example, lets say I have columns n1, n2, n3, n4. and let's say I have a formula:

Output Column: n1

IF [n1] > 10 THEN "TEST" ELSE [n1] ENDIF

and let's say I need to replicate that same formula for each column (n1, n2, n3, n4), replacing [n1] to the relative field (eg. n2). Is there anyway to do something like:

Output Column: n1

IF [*Output Column*] > 10 THEN "TEST" ELSE [*Output Column] ENDIF

Thanks in advance!

Tips and Tricks

Expression

Accepted answers

Storm

The attached workflow may not be the most elegant way, but I think it accomplishes what you're seeking.

It splits the workflow into two parts - those with scores above 100 (presumably "wild" - you could set this to be 110 or whatever) and those not wild.

Then it gets an average grade, by person, of those not wild, unions the two parts (wild and not wild) together, and sets the "wild" scores to the averages of the other ones.

Alteryx_solution_20180716_normalizing_wild_scores.yxmd

All comments

Storm

Do you want to output the "TEST" in column n1 itself - the one that contains the data - or another column that would characterize what you have in n1?

I'm asking because "TEST" is a string, while the > 10 criterion you're using looks for a numeric value....so if you want it to be the same column, then you would possibly want to format the column as string and use the TONUMBER function, e.g:

if tonumber([n1]) > 10 then 'TEST' else [n1] endif

If this is the case, then I'd use the MULTIFIELD tool. Within that tool, select all the n* columns you want and apply a formula similar to:

if tonumber([_currentfield_]) > 10 then 'TEST' else [_currentfield_] endif

JP02

Storm,

Thanks! That worked! However, how can I check to see IF [_CurrentField_] is the same field as I am using in my formula? Example:

Output column: n1

IF [_CurrentField_] = [n1] THEN "" ELSE Average([n1,n2,n3,n4) ENDIF

What I am trying to do: I have a set of scores. Think grades for classes (math = 97, science = 90, lit = 89, etc.). The data input was not very consistant, so sometimes I get wild scores like 580 (probably some typo) so I am developing a formula to change the wild score to an average of the other grades, which looks something like:

IF [_CurrentField_] > 100 THEN Average(n2,n3,n4) ELSE [_CurrentField_] ENDIF

When the "Currentfield" is referencing n2 the Average formula needs to look like Average(n1,n3,n4)

So I am thinking about something like: Average((IF [_CurrentField_] = [n1] THEN "" ELSE [n1] ENDIF), (IF [_CurrentField_] = [n2] THEN "" ELSE [n2] ENDIF), etc...)

I am afraid that what this will actually do is to look at the Value of [_CurrentField_] and see if it equals the Value of [n1], etc., instead of looking to see if [_CurrentField_] is the field n1.

Is that right? Thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703