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; } } }

Flatten an array in Table transformer macro

Eyal Zmora

Hi,

I am using a table transformer macro which wraps a 'Table from CSV' macro.

One of the fields in the excel file contains a list of strings (i.e: "aaa", "bbbb", "cc").

I would like the Table transformer macro to flatten the array and create a separate row for each value in the array. Something similar to Presto's UNNEST function.

I searched for a similar function here, but could not find anything.

Does table transformer support such an operation?

Thanks

Eyal

Accepted answers

Katerina Kovriga {Stiltsoft}

Hi @Eyal Zmora ,

Maybe this workaround will suit you:

Tue 2-1.png

SEARCH / AS @a EX('Col 2'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

Tue 2-2.png

All comments

Katerina Kovriga {Stiltsoft}

Hi @Eyal Zmora ,

Maybe this workaround will suit you:

Tue 2-1.png

SEARCH / AS @a EX('Col 2'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

Tue 2-2.png

Eyal Zmora

Thanks @Katerina Kovriga _Stiltsoft_

The outcome is exactly what I need, but can you please explain the query?

Thanks

Eyal

Katerina Kovriga {Stiltsoft}

This is a workaround from our developers - a mixture of SQL and JavaScript.

We use "," to split the Col 2 into several cells (comma works like a separator here) and return the Col 1 and the corresponding part of the Col 2 to build a new table.

Eyal Zmora

Great stuff! Well done

One more question:

I would like to maintain the original table but I want to apply a FORMATWIKI for each element.

So Row 1/ Col 2 will have 3 hyper links separated by commas, something like this:

FORMATWIKI( ['aaa' | some link]), ['bbb' | some other link], ['ccc' | last link])

Is this possible?

Santhosh Kumar_n505mk

@Katerina Kovriga _Stiltsoft_ , Thanks a lot for this. I tried the same query with the table of more than 2 columns.

SEARCH / AS @a EX('Col 3'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', 'Col 2' AS 'Col 2', _ AS 'Col 3') FROM T1

Col 2 is not fetching the values from the original table. However Col 1 and Col 3 are fine.

Can you pls advise?

Stiltsoft support

Hi Santhosh Kumar,

Seems that you forgot the "@a->" part before the 'Col 2':

SEARCH / AS @a EX('Col 3'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', @a->'Col 2' AS 'Col 2', _ AS 'Col 3') FROM T1

This query should be working fine.

Santhosh Kumar_n505mk

Thanks a lot for this. Works like a charm.

Santhosh Kumar_n505mk

@Stiltsoft support Can i please check how to make it work with some rows which are blank? as the above query doesn't return the rows with wherever col3 is blank. I'm looking to fetch all the rows, even if the col3 is blank.

Can you pls advise?

Welcome!

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

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

automation

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets