Need assistance replacing unmatched fields

[michalikm]

Hello all!

I'm struggling with finding a solution in alteryx that solves the problem I'm having. I have two small data sets attached below (for the purpose of this example) that are somewhat dynamic. I need to take the list of markets in Data2, match them with the list of markets in Data1, and replace any unmatched values with National Markets (listed as a value in Data2). It LOOKS like maybe the dynamic replace tool is what I'm after, but I haven't used it before and can't seem to find an example that shows a use case similar to what I'm after. Any thoughts/help on creating a workflow would be greatly appreciated!

[apathetichell]

Hi! see the attached. this is a join and then a union and then a formula to replace the null()s the nulls() occur where there are no records in common on the join.

 

[michalikm]

This feels very close to what I need, with the difference being that I don't want values that are in Data2, but not in Data1 to be included in the output (with the exception of the "NATIONAL MARKETS" value. Would I just need to remove the left output from the Join to the Union to accomplish this?

[apathetichell]

Are you saying you only want records without a match - or you don't want the data to be repeated for those that do match? If it's the later use a select tool and uncheck market. If it's the former - you only want the left anchor and you can add a new field called "National Markets."

[michalikm]

With the sample inputs provided, my desired output would have the 28 records from the Data1 file, but replace any values that aren't found in Data2, with "NATIONAL MARKETS".

[apathetichell · Accepted solution]

Try this one. earlier set up unioned in the right anchor. I removed that anchor and renamed the fields.

[DawnDuong]

Hi @michalikm 

from the question it sounds like you have not explored the core tools for excel users.

if such vlookup equivalent is a common use case for you, highly recommend that you learn how to use the Join and Union tools with the following resources:

1) interactive lessons (learn > academy > interactive lessons): bite-size 2-3 min only and super easy to follow!

2) try the examples (click on the tool on Alteryx designer ribbon, an “open example” will appear. The Join and Union tools’ examples can show you how different settings give different outcomes with simulated use cases that can be v similar to what you require everyday 

the above 2 till take max 15’ of your time and will gove you back 10x in the time savings in 1 week... 

Dawn

[michalikm]

This worked perfectly! Thank you so much for your help! I've taken the training sessions on understanding the different join tools, but I still struggle with conceptualizing things if I can't find examples that match my specific situation. I really appreciate the help though!

[apathetichell]

@michalikmsome of the weekly challenges are great training tools for join/union configuration. Play around with them - that's how many of us learned...