Split Multiple Columns at the same time with Table Filter plugin

[Lucas Messias]

Hey, I'm trying to split data from an ARRAY into rows for the following table:

So I've tried this Table Transform macro:

SEARCH / AS @a EX('content'->split(","), 'Index'->split(",")) /
RETURN(@a->'Header' AS 'Header', _ AS 'content', _ AS 'Index') FROM T1

But it isn't working, that is what I'm getting:

My end goal is to split content and Index columns simultaneously, so I can later PIVOT this table to make the Header column become the table header, Index would be the group by operator and content the main row values (something like group by Index MAX(content)).

Is it possible to do?

Thank you!

[Stiltsoft support · Accepted solution]

Hi @Lucas Messias ,

You may try the following SQL query:

SET @indexes = (SELECT SUM('Index'+",")->split(",") FROM T1);

SEARCH / AS @a EX('Content'->split(",")) /
RETURN(@a->'Header' AS 'Header', _ AS 'Content', @indexes->shift() AS 'Index')
FROM T1

Please note that the number of comma separated strings within the 'Content' cells should match the number of indexes.

So, your tabular array is not best practice at all, there could be errors due to personal mistakes. It is always better to create proper tables firsthand (a separate row for each entry): you'll be able to filter out empty cells and group your tables easily.  

[Lucas Messias]

Hey, thank you for your quick response. I do agree that using ARRAY may not be the most ideal solution, but it was the only workaround I found for achieving a specific functionality.

Basically, I aim to implement a Dropdown menu that dynamically alters the columns of a table based on the user's selection. Together with that, I need to merge different tables as well. So, for example, the user choose "Preset 1" option on the dropdown, this will make the macro merge table 1 and 2 and generate the new header. Then he chose "Preset 2" and now the merge is between table 1 and 3. Consequently, I had to use ARRAY to pivot and unpivot the data, facilitating changes to the header. I'm wondering if there might be an alternative approach to achieve this. What do you think?

Unfortunately, the provided solution is not returning rows, even though both Index and Content have the same number of commas.

I appreciate any insights or suggestions you may have regarding this issue.

Thank you.

Screenshots of my current Table Toolbox:

[Stiltsoft support]

Hi @Lucas Messias ,

The provided query is a working one, please check the names of your columns.

As I see on the first screenshot, you have 'Index', 'Header', 'content'. But in the query you use 'Index', 'Header' and 'Content'. Uppercase and lowercase letters used for the columns' names are treated differently.

What concerns the idea in general, it seems rather complex. Maybe the easier way will be to combine (merge) the 2rd, 3rd, 4th, etc. tables firsthand and wrap the result table in the Table Filter macro. The user may set a filter and then this filtered table will be merged with the first table.

Or maybe is better to use the Table Spreadsheet macro where you can use Excel-like filters, pivot tables and can refer to different cells from the current or other sheets and create different views of the same table.

You may reach to our support portal as well. It is confidential, you may share your real data there and we'll be able to discuss an easier solution together.

[Lucas Messias]

You're right, "content" was wrong, but it's still not "exploding" the Index column.

[Stiltsoft support]

Sorry, my bad: tested the query on Data Center and for Cloud it works slightly differently.

Please use the following SQL:

SET @indexes = (SELECT SUM(TEXT('Index')+",")->split(",") FROM T1);

SEARCH / AS @a EX('Content'->split(",")) /
RETURN(@a->'Header' AS 'Header', _ AS 'Content', @indexes->shift() AS 'Index')
FROM T1

[Lucas Messias]

Worked perfectly! Thank you very much.