Changing Value in a Field Based on Condition

[bwortham32]

Good morning,

I know there are several ways to achieve what I'm attempting, but I can't seem to get any of them to work without messing up the rest of my data.  I have a field named "Company" in V_String format which contains 0 values.  Whenever there is a 0 in this field, I want to change this to "UNASSIGNED" without messing with any of my other Company values. 

I've tried Dynamic Replace, Switch, Conditional IF, etc.  Can anyone tell me the most logical way to do this without messing up the rest of my data?

Thanks!

[MarqueeCrew · Accepted solution]

Hi @bwortham32,

How about a formula tool with the following statement:

IIF([Company]==0,"Unassigned",[Company])

Thanks,

Mark

[roadhouse · Accepted solution]

This sounds like a job for the RegEx tool. Take a look at my attached workflow.

Explanation of the regular expression ^[0]$:

^ - The beginning of the string

[0] - This is the zero

$ - The end of the string

[bwortham32]

Thank you!  Newbie question, but I am learning.  Have a great day.

[MarqueeCrew]

@bwortham32,

Newbie questions are the BEST!  I get a fresh perspective on the use of Alteryx and get to see where potential issues are for my clients in their initial use of Alteryx.  When you get multiple answers posted on a question, it is an opportunity to see how other users approach similar problems in very unique ways.

There isn't always a BEST answer and in reading posts and replies you'll get to learn lots.  

Cheers to you for asking!

Mark

[bwortham32]

@MarqueeCrew

Great perspective.  I have one more thing to throw your way (this is the last)!  I'll try to explain this as easily as possible:

I want to take the sum of an entire Field.... Then I want to take the reciprocal value of that sum and place it into the same field based on a condition within the field.

For example, say I have two fields:  Company, Revenue - Company contains values 0-100 as well as an "Unassigned" field.  I want to take the sum of the Revenue Field and reverse the sign, then place this result back into the Revenue Field where Company equals "Unassigned."  Is this possible?  Not sure how well I explained this scenario. Lol.

Thanks

[MarqueeCrew]

@bwortham32,

I am confused.  Seeing sample data would help tremendously.

But if the data looks like:

Company, Revenue
1, 500
2, 505
Unassigned, 400

The formula of:

IIF([Company]='Unassigned',[Revenue] * -1,[Revenue])

Would reverse the sign on revenue in the case of Unassigned.

I'd be happy to update the solution if the data problem is different.

[bwortham32]

Hi Marquee,

I may have a better example of what I'm trying to do here.. Let's say this is my original output:

State	Emp No	Job	Income
TX	1234567	Trainer	1000
OK	1234569	Driver	800
KS	1234534	Sales	1200

I want to create a file with the exact same dimensions (State, Emp No, Job) and place the reciprocal income, which will be negative, into a row where the Job is UNASSIGNED.  So it will look like this:

State	Emp No	Job	Income
TX	1234567	Trainer	1000
TX	1234567	UNASSIGNED	-1000
OK	1234569	Driver	800
OK	1234569	UNASSIGNED	-800
KS	1234534	Sales	1200
KS	1234534	UNASSIGNED	-1200

Any ideas?

Thanks!