How to remove next repeated value in each row

Question

Hi All,

Let's say I have id column (which might not be unique) and 6 levels of product categories where each level is one level deeper than the previous one, like a hierarchy tree.

However I can have duplicates as shown below, product with id=143 in first line has the same value since cat3 - FACTORY SERVICES to the end, category 6.

I would like to remove only those duplicated values in each row to obtain a table as below:

So I need a duplicates checker in each row.

Is it anyhow possible in Alteryx ? Which steps should I take to obtain such table as above ?

After that I would like to merge those 6 categories in to one column but already know that it's possible.

Below in attachment I put example of a data.

Any help will be much appreciated.

Regards

remove_repeated_values.xlsx

atcodedog05 · Accepted Answer

Hi @Rafal_Pietrak

Here is how you can do it.

Workflow:

1. Transpose columns to rows with ID as key

2. Use sample tool to keep only 1st value of every ID

3. Sort ascending based on id and col name to get in order

4. Crosstab it back to table with ID as key

Hope this helps🙂

Kenda · Accepted Answer

Hi @Rafal_Pietrak

The key here is to transpose then cross tab your data. In the attached workflow, you'll see I first transposed the data so that all of your cat variables and values were in the same column. This allowed me to use a Unique tool to keep only the first unique cat value for each id. Note I also added a record ID tool at the beginning since you said that the given id field might not be unique. To get the data back to the original format, I just used a CrossTab tool.

Hope this helps!

Shifty · Accepted Answer

@Rafal_Pietrak

@atcodedog05 & @Kenda  both nailed it as usual but I achieved the same result in a slightly different way and I'm proud of myself so have to post! 😁

Hope your query is addressed.  Cheers!

Shifty