community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

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

How to replace repeated fields in a column with blanks leaving unique row.

Saravanan13
8 - Asteroid
‎03-14-2024 12:35 PM

Hello Team,

 

I am looking to remove repeated fields in a row with blanks as shown below.

 

Input - 

 

Test1Test2Test3Test4Test5Test6Test7Test8Test9
111112121212024 06Y6/12/20066/12/2024BeneficiaryY2024
222212121212024 06Y6/12/20066/12/2024BeneficiaryY2024
333312121212024 06Y6/12/20066/12/2024BeneficiaryY2024
4444232323232024 07Y5/3/20066/12/2024Beneficial OwnerY2024
5555232323232024 07Y5/3/20066/12/2024Beneficial OwnerN2024
6666232323232024 07Y5/3/20066/12/2024Beneficial OwnerN2024
7777232323232024 07Y5/3/20066/12/2024Beneficial OwnerY2024
8888344455452024 08Y7/21/20067/21/2024NomineeY2024
9999344455452024 08Y7/21/20067/21/2024NomineeY2024
878787344455452024 08Y7/21/20067/21/2024NomineeY2024
878788344455452024 08Y7/21/20067/21/2024NomineeY2024
878789344455452024 08Y7/21/20067/21/2024NomineeY2024
878790898989892024 09Y9/2/20069/2/2024BeneficiaryY2024
878791898989892024 09Y9/2/20069/2/2024BeneficiaryY2024
878792898989892024 09Y9/2/20069/2/2024BeneficiaryY2024
878793898989892024 09Y9/2/20069/2/2024BeneficiaryY2024
878794454545452024 10Y9/16/20069/16/2024Beneficial OwnerY2024
878795788777882024 11Y6/30/20066/30/2024NomineeY2024

 

Required output -

 

Test1Test2Test3Test4Test5Test6Test7Test8Test9
111112121212024 06Y6/12/20066/12/2024BeneficiaryY2024
2222        
3333        
4444232323232024 07Y5/3/20066/12/2024Beneficial OwnerY2024
5555        
6666        
7777        
8888344455452024 08Y7/21/20067/21/2024NomineeY2024
9999        
878787        
878788        
878789        
878790898989892024 09Y9/2/20069/2/2024BeneficiaryY2024
878791        
878792        
878793        
878794454545452024 10Y9/16/20069/16/2024Beneficial OwnerY2024
878795788777882024 11Y6/30/20066/30/2024NomineeY2024
Labels:
Reply
0 Likes
5 REPLIES 5
CharlieS
17 - Castor
17 - Castor
‎03-14-2024 12:41 PM

This sounds like a job for the Multi-Row Formula tool!
 Multi-Row Formula Tool (alteryx.com)

 

With this tool you can modify a field based on the rows above (minus rows) or below (plus rows). So if the current row is the same as "row-1" (the row above it) then set it to null. You'll use conditional expressions in this tool to empty out repeating row values in each field.

 

Check it out and let us know if you have questions. 

Reply
0 Likes
Saravanan13
8 - Asteroid
‎03-14-2024 12:51 PM

Thanks for the reply, I tried with Multi-Row Formula tool but looks like we can update only one row at a time. Can you assist please. I haven't used this tool before

Reply
0 Likes
HomesickSurfer
12 - Quasar
‎03-14-2024 01:23 PM

Hi @Saravanan13 

 

Here's how I'd do it with Mode and several Group By actions, matching with record Id and then sorted.

Workflow attached.

 

Note:  your expected results are incorrectly excluding the 5555 Test1 as a similar Test8 has a value of 'Y'.

Capture.PNGCapture2.PNG

group tests.yxmd
Reply
0 Likes
Saravanan13
8 - Asteroid
‎03-14-2024 01:44 PM

Thanks for the reply, One issue with your output is in test1 column I need all values. One field in test 2 can be linked to multiple fields in test1. Can you fix it. Please refer to the required output

Reply
0 Likes
HomesickSurfer
12 - Quasar
‎03-14-2024 02:09 PM

Hi @Saravanan13 

 

Revised flow attached.

group tests.yxmd
Reply
0 Likes
learn more about the python upgrade coming in may 2024
Labels