VLOOKUP Type Question

[GK13]

Hello Alteryx Community,

I am new to Alteryx and need some assistance with a workflow.

Scenario: I am trying to change column C based on data in column A.  If column A has a "1" AND column C says "Yellow", then column C needs to be changed to say "Yellow" anytime there is a "1" in column A.

Attached is a screenshot of the example along with anticipated results.

[AndrewDMerrill]

The issue you run into is how do you determine which color takes priority? Take the example below (which is based off of your dataset)

Number	Product	Color
1	Banana	Yellow
1	Peach	Red

Why pick yellow instead of red?

This kind of data manipulation is very easy with Alteryx, but you need to have well-defined rules, otherwise your output will be useless because data quality is low.

[GK13]

In this scenario I do in fact want Yellow to be the result.

(This is a fictitious scenario for a larger and more complex dataset)

[Prometheus]

@GK13 Try this expression in a Formula tool: 

if [Number]=1

then 'Yellow'

else [Color]

endif

 

[AndrewDMerrill]

If all you are looking to do is hard-code a change to data based on data in another column, then the formula tool is all you need:

The problem still stands though, how do you "KNOW" that yellow is what you want. This is critical when developing workflows in Alteryx because the answer to that question can be the difference between a simple workflow like this, and a massive web of tools.

[apathetichell]

attach a sample tool to your workflow. put the sample tool in N=1 and group by Number.

connect a join tool.

your sample tool is the left anchor, your original datastream is your right anchor.

deselect duplicates

join on number.

DONE.

[GK13]

Thanks everyone for your responses!  This provided the outcome I was looking for. 

Now, another similar scenario that I would like assistance on is the following hypothetical dataset:  Is there a way to automate this without entering any specific number.  What I'm looking to accomplish in this scenario is that for anytime there is a "Yes" in the 'Availability column', I would like that associated number to maintain the "Yes" throughout the dataset.  I am unable to list out all of the numbers that need to be changed because I would not know...unless it gets sorted in some way?

The highlighted portion is what I'm looking to bring back.

[AndrewDMerrill]

Here is a sample workflow that accomplishes your use case!

[GK13]

This is exactly what I was looking for, thanks!  One additional question/scenario...I apologize, I'm so new to this.

If I want to accomplish the same thing but for more than one column at a time, how would I go about this?  I can create the same workflow multiple times for the different column, but I don't know how I would combine it all together for one output.