How to replace value with another value, or leave as original value.

Question

I am trying to do some data scrubbing and I want to find the best way to assign a specific account # with a tax type.

For Example:

Account # 12345 - Tax Type MISC1

Account # 98765 - Tax Type N/R

I am trying to use the ELSE IF function: IF [Acct] = '98765' THEN [Tax Type] = 'N/R' ELSE ________END IF

My question is:  What do I put for the ELSE?  I want the value to remain as its current value  if it is NOT the specified account #.  I keep getting the Malformed IF statement, when I don't include ELSE.

Example:

Account 22565, the Tax Type should remain Current Value

Account 12345, the Tax Type should be MISC1

Account 98765, the Tax Type should be N/R

Attached is some fake data for your reference. Any help is appreciated!

Acct_Tax Type.yxmd

Felipe_Ribeir0 · Accepted Answer

Hi @dianawine

try this: IF [Acct] = '98765' THEN 'N/R' ELSE [Tax Type] ENDIF

ChrisTX · Accepted Answer

Use a Formula tool to update the value of field [Tax Type]

IF [Acct] = '98765' THEN 'N/R' ELSE [Tax Type] ENDIF

IraWatt · Accepted Answer

Hey @dianawine,

If you want to keep the value, the same you just need to say else use the value in the column already there. This will look like this:

IF [Acct] = '98765' THEN 'N/R' ELSE [Acct] ENDIF

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20Expressions

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

dianawine · Accepted Answer

For some reason, when I use this code, instead of using the 'N/R' it leaves the column as 0.  Any idea how to fix?

DataNath · Accepted Answer

The reason @IraWatt's expression brings back a 0 is because of the part in bold:

IF [Acct] = '98765' THEN [Tax Type] = 'N/R' ELSE [Tax Type] ENDIF

The expression should just be:

IF [Acct] = '98765' THEN 'N/R' ELSE [Tax Type] ENDIF

Having the [Tax Type] = part acts as an extra, Boolean check within the expression and so you're basically saying if the account is 98765 then is the tax type N/R? We can't open the attached data so I've just mocked up an example below to show that when the Tax Type isn't N/R it returns a 0 (i.e. the Boolean check is false), and when it is N/R then it's true (-1). The reason this doesn't apply to the other [Acct] is because you're not naming it in the initial 'if':