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

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

dianawine
7 - Meteor

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!

8 REPLIES 8
Felipe_Ribeir0
16 - Nebula

Hi @dianawine 

 

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

 

Felipe_Ribeir0_0-1667241475504.png

 

ChrisTX
15 - Aurora

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

 

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

 

 

IraWatt
17 - Castor
17 - Castor

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

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
17 - Castor
17 - Castor

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

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
15 - Aurora

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
17 - Castor

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

 

DataNath_0-1667298227785.pngDataNath_1-1667298234679.pngDataNath_2-1667298246224.pngDataNath_3-1667298252799.png

Labels