Grouping Logic

[czjames87]

I am relatively new to Alteryx and would really appreciate some help with the problem I am having with the attached workflow. I am attempting to find and replace the customer name in the destination name column with a group name in the group_name column. However, I need a formula that allows for the destination name to remain the same if the customer name listed in the destination_name column is not one listed in the grouping logic column. Essentially, I want the logic to say "if destination_name is in grouping name column then use group name if not use destination_name". Any help would be greatly appreciated. 

[DavidP]

Hi @czjames87 can you please post an example of the Destination name column. We can't see it because of the failed database connection in the Dynamic Input Tool.

[czjames87]

The quotes have been removed with a data cleansing tool along with any whitespace

[czjames87]

sorry about that. here is the destination name column example 

[DavidP]

Hi @czjames87 

Can you please mock up an example in Excel that shows 5-10 rows of data with the columns Group Logic and Group Name in one set and Destination Name in another and a final Column Desired Output next to Destination Name.

Then mock up some data to represent the different scenarios you want covered and what the resultant output should be.

Hopefully we can then better understand the problem and come up with a solution.

[BrianR]

With the slice of data you showed, the Find Replace tool finds only a few matches, but give this a try - instead of the Filter tool at the end of your workflow, replace that with a Formula tool - this formula should evaluate your Group_Name column. If the Find Replace DID find a match, it will reflect here - if not, it will set it to the destination_name column:

[czjames87]

thank you for your help. The logic seems to be working partially. I forgot to add that i want to make this a new column. Does that matter with the logic used?  As of right now it appears that the logic is working for some data but not all of it is grouping. Example below. The destination name should read the grouping name BJ's Wholesale Club though. 

[BrianR · Accepted solution]

I played around a bit with the Find Replace configuration - if I change it to this - where the Find Value is set to Group_Name, it correctly picks up BJ'S WHOLESALE CLUB. To account for a new field, take a look at what I mocked up - of course, I don't have access to your DB connection, so I just typed in your example data manually.

This version adds in a new column, the same logic can be used on existing columns or completely new ones. In order to increase your matches, I would augment your LOCATION GROUPING table to add more examples for lookup purposes. For example, in your destination_name file, I see a bunch of references to locations beginning with AE...but there is no lookup in the LOCATION GROUPING for AE, so it does not match. 

Take a look, hopefully this gets you at least part of the way, and if you can add more lookup values, it should increase your "hit rate". 

~~~

Brian

If this solves your issue, please consider marking this as Complete so others can find this as well.

[czjames87]

that worked.. Thank you for your help. I appreciate it.