Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Changing Value in a Field Based on Condition

Highlighted
7 - Meteor

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!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @bwortham32,

 

How about a formula tool with the following statement:

 

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

 

Thanks,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner

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

Highlighted
7 - Meteor

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
7 - Meteor

@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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
7 - Meteor

Hi Marquee,

 

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

 

StateEmp NoJobIncome
TX1234567Trainer1000
OK1234569Driver800
KS1234534Sales1200

 

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:

 

StateEmp NoJobIncome
TX1234567Trainer1000
TX1234567UNASSIGNED-1000
OK1234569Driver800
OK1234569UNASSIGNED-800
KS1234534Sales1200
KS1234534UNASSIGNED-1200

 

Any ideas?

 

Thanks!

Labels