Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find & Replace

skychar
6 - Meteoroid

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.

5 REPLIES 5
BenMoss
ACE Emeritus
ACE Emeritus

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
6 - Meteoroid

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. 

 

skychar_0-1588054538670.png

 

BenMoss
ACE Emeritus
ACE Emeritus

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

 

ben

danilang
19 - Altair
19 - Altair

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
5 - Atom

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

Labels