Formula Tool

[razzy]

Hi all,

I want to use the formula tool to create a field from two existing fields, the calculation of the field will be like

let say the field is "C" , from existing fields " A" and "B"

         C=A-B iff A is greater than B

   else C=B-A ; B is greater than A

This is pretty easy in any programming language , i just too new to alteryx

How do i go about to get this formula;

thanks

[LordNeilLord]

Hey @razzy

Try:

IF A>B THEN A-B
ELSEIF B>A THEN B-A
ELSE NULL()
ENDIF

You don't necessarily need the null but I usually put it in to capture any that don't apply to the rule

If statements must always follow IF THEN ELSE ENDIF

NEIL

[david_fetters]

If youre looking for IIF or a ternary operator, then Alteryx has it.  See: https://help.alteryx.com/9.5/Reference/Functions.htm

Just create a formula tool and new field C with the following formula

IIF([A]>[B],[A]-[B],[B]-[A])

[razzy]

So the syntax worked well with no error, but i am not getting the output on the created field to get the desired result on each field, at the first run it came up as null right through on the column then i tried adjusting  the data types of the field to match that of the calculated field i still got no output.

[LordNeilLord]

Hey @razzy

Can you share an example dataset and the required output?

Neil

[razzy]

START_DOSE  END_DOSE  DOSE CHANGE
25                      5                           20

So dose change is the calculated field and should be outputting  (start_dose-End_dose) if Start_dose is greater than end_dose , else end_dose-start dose  

[razzy]

I wrote this in the formula tool.

one of the beginning errors i had was that the field is not contained in the records,  and i used add a column the data preview field was default null,

IF [START_DOSE] >[END_DOSE] THEN [START_DOSE]-[END_DOSE]
ELSEIF [END_DOSE]>[END_DOSE] THEN [END_DOSE]-[START_DOSE]
ELSE Null()
ENDIF

[bharti_dalal · Accepted solution]

hi @razzy, Did you verify the data type of your fields? i hope they are not strings . I am attaching the solved workflow with two solutions.