Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,295,762
Community Members
 
Community Events
165
Community Groups

Flatten an array in Table transformer macro

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

1 answer

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

Thanks @Katerina Kovriga _Stiltsoft_ 

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

 

Thanks

Eyal

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. 

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?

I can suggest using the following structure:

Tue 3-1.jpg

Instead of the Table 1 you'll have the Table from CSV macro. The Table Transformer 1 is required to work with your array - it contains just the query that I've mentioned above.

The second table is optional and is needed if you want to combine the rows of your first modified table with different text to create links, add some info, etc.

The Table Transformer 2 is used for the FORMATWIKI functions. You may check its documentation here.

The Table Transformer 2 will see the output of the Table Transformer 1 as a separate "solid" table without any hidden modifications. And you'll be able to proceed working with it like with a plain manually created table (like it wasn't unnested by the TT1 and was just created from the scratch).

 

If the question was if you could add different links to the original array without unnesting (several links into one cell ['aaa' | some link]), ['bbb' | some other link], ['ccc' | last link]), then I'm afraid that this case is not fully possible - I really can't find a simple and beautiful solution. It will be necessary to split the cells, add links (match them by some criteria because they are different), combine the cells back into one cell.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

953 views 15 27
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you