nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer: Remove duplicate values from a comma separated list

Stephan Bauer_jwjrj0

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 ?

Accepted answers

Stephan Bauer_jwjrj0

@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

All comments

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_jwjrj0

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

Stephan Bauer_jwjrj0

@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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets