Table Transformer: Remove duplicate values from a comma separated list

[Stephan Bauer]

Hi there,

I need a way to remove duplicate values from comma separated lists within a table.

I have a table like this:

| Key     | Items                       |

--------------------------------

| Key-1 | item1, item1, item2 |

| Key-2 | item3, item3            |

| Key-3 | item4                       |

What i would like to have in the end is a table without duplicate values in the "Items" column:

| Key     | Items            |

-------------------------

| Key-1 | item1, item2 |

| Key-2 | item3            |

| Key-3 | item4            |

Any ideas ?

[Stephan Bauer · Accepted solution]

@Stiltsoft support & @Jim Knepley - ReleaseTEAM 

Thanks for challenging me 😜

I was thinking way to complex , this piece of code does the job:

SELECT *,
       MATCH_REGEXP(T1.'Teams',"\b(\w+)\b(?!.*\b\1\b)", "g") as 'Teams'
  FROM T1

I think this should also work but we are on 11.4 atm so I have no way to test it

SELECT *,
       REGEXP_REPLACE(T1.'Teams',"\b(\w+)\b(?=.*\b\1\b)", "g") as 'Teams'
  FROM T1

[Stiltsoft support]

An excellent solution indeed! Nice done!

The second query is also a working one:

SELECT *,
REGEXP_REPLACE(T1.'Teams',"\b(\w+)\b(?=.*\b\1\b)", "", "g") as 'Teams'
FROM T1

Just not to miss the third operator - to what we are changing our match.

[Jim Knepley - ReleaseTEAM]

Hi @Stephan Bauer 

There are probably several ways to approach this, but this is what comes to mind:

• The first step is to split the cells up so there's one item per cell (you'll have duplicated keys) instead of a comma-separated list.
• Use Table Transformer SQL to aggregate the duplicate items.

Or... depending on your workflow, maybe pre-process the table data before creating the table.

[Stiltsoft support]

Thank you @Jim Knepley - ReleaseTEAM - it's exactly what we were going to suggest!

[Stiltsoft support]

Hi @Stephan Bauer ,

You may indeed split the array using the first link given by @Jim Knepley - ReleaseTEAM , then you may create an additional 3rd column 'Key+Item' and concatenate 'Key' and 'Item' columns.

Then you leave only rows where 'Key+Item' cells are unique (using the DISTINCT function).

After that you group the 'Key' and 'Item' columns back by the 'Key' column (using another Table Transformer macro or the Pivot Table macro).

[Stephan Bauer]

Thanks for the quick answers.

Well, I simplified my table to make it easier to see my issue and, in the hope, a simple answer exists.

It looks like I need to give some boundaries for it:

There are a lot more columns in my real table

There are already a lot of other table transformers around this one and I would love to make things easier to read and not harder

The pages are heavily used and so there a frequent changes done to it (especially columns) so maintainability is an issue

Therefore, I am really looking for a way to:

*) To do this within a single table transformer

*) Avoid anything that makes me list all table columns manually

With SQL this should be doable with something like this:

SELECT

    *,

    (SELECT STRING_AGG(DISTINCT value, ',')

     FROM STRING_SPLIT(T1.Teams, ',') AS SplitValues(value)) AS Teams

FROM T1

But I didn't get it running with the functions available in the table transformer universe

Is a way to ->split() DISTINCT and ->join() with the table transformer?

Thanks in advance

[Stiltsoft support]

The macro is based on the AlaSQL library with tiny bits of JavaScript. You can check the list of supported functions here.

Seems that the described workaround is the only option as for now.