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

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Eyal Zmora ,

Maybe this workaround will suit you:

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

[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?

[Katerina Kovriga {Stiltsoft}]

I can suggest using the following structure:

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.

[Eyal Zmora]

Thanks @Katerina Kovriga {Stiltsoft} 

[Santhosh Kumar]

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

Thanks a lot for this. Works like a charm.

[Santhosh Kumar]

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

[Stiltsoft support]

Hi @Santhosh Kumar ,

You may use another Table Transformer macro between the source table and the existing Transformer.

There you use the following SQL query:

SELECT 'Col 1', 'Col 2',
CASE
WHEN 'Col 3' IS NULL
THEN "-"
ELSE 'Col 3'
END
AS 'Col 3'
FROM T*

As you can see, we check if the 'Col 3' cell is empty and put "-" symbol (or whatever string suits you) if it is so. 

[Santhosh Kumar]

Brilliant, you guys are super quick and gave the perfect solution. Thanks. 

[Timothy Padgett]

I have a somewhat different source table, which comes from another confluence page and looks like:

How can I split this into three rows?  I can't seem to adapt the "SEARCH..>RETURN" code to work, maybe mostly because I can't figure out how to split by new lines?

[Stiltsoft support]

Hi @Timothy Padgett ,

Indeed the SPLIT() function works with strings in the AlaSQL library, so you need to have some "visible" delimiter that will help you to separate items (comma, dot, semicolon, etc.).

The end of line is not recognized here: the "\n" expression will be treated as a simple string.

The same concerns the "@" at the beginning of the name mention: the symbol is not visible by the Table Transformer macro.

[Timothy Padgett]

So, is there any way to do this? Some sort of regex pattern matching, or something else if not this specific split() function approach?

[Stiltsoft support]

Unfortunately, can't think of anything matching. As I see, you have names and surnames: different length, two or three words, no delimiters or some special patterns, different number of entries.

The simplest way is to introduce delimiters in your source table:

1. Person One;
2. Person Two;
3. Person Three

Or you may use the Table Spreadsheet macro instead of native Confluence tables.

You'll be able to keep the original table on the Sheet1, for example, and use the Sheet2 to refer to the cells from the Sheet1 and built whatever table you need (reassemble the source table like you do in Excel).