Replace value in column with another value from same column

[BPA1982]

Hi there,

I’m pretty new to Alteryx and am having difficulty with a small problem -  I was wondering how to go about doing the following:

I’m basically trying to update a value in the same field where the ‘Name’ value is the same. So, using the simple example below if the name of the product is the same, (e.g. apples), then how would I go about changing the value of the 5th record ‘Comments’ value, to be the value of the 1st record ‘Comments’ value as shown below.

Old table

 So the output should look something like the following:

New table

I’ve tried using formulas, Regex, match and replace, and multi-row and multi-field formulae, but all to no avail.

I’ve been trying to figure this out for a few days straight now and can’t seem to get anywhere with it.

Any help would be greatly appreciated.

Thank you in advance and kind regards,

Ben

[patrick_digan · Accepted solution]

@BPA1982 I've attached an example with several different ways to attack this problem.

Starting on the left, you can use a multi-row tool and group by the name field. This will work if your data is already in order. If it's not in order, you could always put a sort before your multi-row.

Next you can sort your data by name, and then use a multi-row tool without the grouping feature. 

You could also use a summarize to group by Name and choose the first comment, and then join this back to your original data set. Again your data will need to be sorted so that the first comment is the one you want.

You could also use a unique tool to grab the first record and join the comment back to your original data set. our data will need to be sorted so that the first comment is the one you want.

You would want to sort at the end if you want your data back in the original order.

[BPA1982]

Hi @patrick_digan

Thank you very much for your quick reply and attached workflow with the different ways to approach the problem. It works great. Much appreciated!

Kind regards, 

Ben