Find & Replace

[skychar]

Hi,

I am quite new to Alteryx.  I have a very messy data file. Each client has a client ID. However, as this file is consolidated from different regions, some customer data is different, some customer data is empty despite the customer is the same. 

I would like to choose one of the data, regardless the correctness of the data and apply the same to those customer with the same ID. 

I have attached a sample file, do note that i have many columns of customer data to be find & replace than being shown. 

I have tried Multi-row formula to group the customer ID and try to apply formula, i.e. if is null. However, if the field is not null, then the data will not be changed and same customer ID may results in two different address. 

Could anyone suggest a better method? Thanks in advance for the assistance.

[BenMoss]

Personally I would approach the problem as follows;

1) Use the summerze tool, group by ID and select the 'First' of each of the columns for your data. This will take the values from the very first instance of an ID. 

2) Use a join tool to bring these values in line with every record with each ID

You could even add a filter or tool before the summerize to ensure you only take the values that are not empty as the ones that you want to apply against the data.

[skychar]

Hi @BenMoss,

Not sure if I understand you well, can i just check with you how do I select the 'First' of each of the columns?  I have chosen the group by ID. but I am not sure how to select the  'First' of each columns. The results churns on to be only one column. 

[BenMoss · Accepted solution]

Select the field, hit add and the first option should be available in the list of actions you can apply.

ben

[danilang]

Hi @skychar ,

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!

Dan

[sreedhar1236154]

I have some account names starting with 1/ and some are not. 

I want to replace 1/ as blank if exists in the name.

How to do it