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

Remove duplicate values within a row

TB_Mylene
7 - Meteor

Hi, I have thousands of records that I need the capability to remove duplicate values in within a row. I need to identify the different type of designs a building has had from newest to oldest throughout the life of the building. 

 

Here's an example of the input.

 

TB_Mylene_0-1664076493634.png

 

Here's how I would like the output to be.

TB_Mylene_1-1664076522103.png

 

Any advise is appreciated. 

 

Thanks in advance.

7 REPLIES 7
Scott_Snowman
10 - Fireball

One option:

  1. Use  a Transpose tool with Store Number as the key column
  2. Use a Unique tool and de-dupe such that you have unique Store Number / Value columns.
  3. Use a Filter tool and remove any records with [Value] empty.
  4. Use a Formula tool and add a New Field, [Count] equal to 1 for every record.
  5. Use a Running Total tool grouping by Store Number and summing the [Count] Field so you end up with an incrementing number starting at 1 that increases within a Store Number, and resets to 1 when you hit a new Store Number.
  6. Form a new field, [Sequence] as the string "Sequence " concatenated with the Running Count total (i.e. "Sequence 1", "Sequence 2" etc.)
  7. Finally use a Cross Tab tool, grouped on Store Number, with the [Sequence] field as your column headers and the [Value] field as your column values.
Qiu
20 - Arcturus
20 - Arcturus

@TB_Mylene 
End up a length one since have to consider the columns names.

0905-TB_Mylene.PNG

flying008
14 - Magnetar

Hi, @TB_Mylene 

 

A other way to filter the duplicate columns.

 

录制_2022_09_26_08_11_43_148.gif

 

The Multi-Row Formula:

iif([Concat_Value]=[Row-1:Concat_Value],Null(),1)

 

********

If it helped you to solved, maybe you can  accept it as a solution and give a like to answer.  😁

TB_Mylene
7 - Meteor

This solution worked perfect! Exactly what I needed to do. Thank you so much!

TB_Mylene
7 - Meteor

Thank you for this information. 

TB_Mylene
7 - Meteor

This also worked! Thank you for showing me another way.

Qiu
20 - Arcturus
20 - Arcturus

@TB_Mylene 
Thank you for your feedback and gald it helps.

Labels