Find & Replace

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.

Book1.xlsx

Output

Accepted answers

BenMoss

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

ben

All comments

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703