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

[dianawine]

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!

[Felipe_Ribeir0 · Accepted solution]

Hi @dianawine 

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

[ChrisTX · Accepted solution]

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

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

[IraWatt · Accepted solution]

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%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[dianawine · Accepted solution]

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?

[IraWatt]

Hey @dianawine would you be able to attach an example dataset for context? The workflow you attached links to data on your computer and can't be accessed.

[dianawine]

I think I got it!  I was able to use the code: IIF([Acct] = "98765", "N/R", [Tax Type])

Not sure what the difference is, but the IIF definitely worked for me vs the IF.

[ChrisTX]

Here's a list of all functions:  https://help.alteryx.com/current/designer/functions

When I started using Alteryx, reviewing the function examples helped me when writing formulas.

Chris

[DataNath · Accepted solution]

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':