Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Relative field references

JP02
7 - Meteor

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!

JC

5 REPLIES 5
Storm
9 - Comet

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

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!

JP02
7 - Meteor

Storm, 

 

After working with it further, that is not going to work the way I think it should.

 

What happens is if the [_currentfield_] = [n1] THEN Null() ELSE Average(n1,n2,n3,n4) ENDIF.... the Average expression treats the null value as a 0, which means now it is averaging (0,n1,n2,n3,n4).

 

Any ideas on how to have the output go from null = 0 to "skip this value"?  I am going to need to think through this some more.

 

Thanks.

 

Storm
9 - Comet

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.

JP02
7 - Meteor

Thanks, Storm!

 

That workflow really helped me get started in the right direction.  Really appreciate it!

 

Jacob

Labels