Table Transfomer: Sorting date with concatenate cells

Question

Hello everyone,

I'm gathering information from a Jira project which I would to consolidate in a Confluence Table thanks to Table Transformer. Here is the current display I have:

So, this is the result I'm looking for. However, the date in Start Date aren't sorted correctly if I'm using SQL or even Table Transformer settings to sort them.

Do you have any suggestion?

Many thanks,

Aurélien

Stiltsoft support · Answer

You may not share your sensitive data via the support portal, the confidentiality is just another benefit vs the public Community platform.

All the tickets coming to our portal are registered, not missed out and influence on our internal roadmap. So, if you have any questions regarding our macros, please raise a support request and we'll try to help.

To sort your dates you may use the ORDER BY clause inside the Table Transformer macro. To group your sorted data with the remaining cell borders, you may use the Pivot Table macro on top.

Aurélien Thazet · Answer

Hello,

Thanks for your reply.

As I said, I understand that your portal is completely confidential, but I've checked and my company doesn't allow it. I just can't go against my employer's policy.

Also, I think it is a great idea to ask this kind of question in public in order to share knowledge. I don't think I'm the only one with such questions and goals. I think you have a great tool and it is interesting to build a community around it.

Anyway, about your suggestions: I've already tried them and I'm not surprised that it doesn't work when I merge the cells. So I was just wondering if there are any workarounds to get this kind of final display:

Many thanks,

Stiltsoft support · Answer

Hi @Aurélien Thazet ,

Please refer to our support if you have any questions regarding the macros provided by the Table Filter, Charts & Spreadsheets for Confluence app.

Seems that we've mentioned it already: the support portal is confidential so you'll be able to share your page storage so that we'll be able to reproduce your environment.

Meanwhile, the sorting within the Table Transformer macro is done via the ORDER BY clause:

SELECT * FROM T*
ORDER BY 'Date' ASC

or

SELECT * FROM T*
ORDER BY 'Date' DESC

The date format in the Setting tab -> Date format field of the Table Transformer macro should correspond the date format from your source table.

Also it's an obvious moment that you sort dates on the stage where you have one date per cell. At first you sort your dates and then you may use other queries to concatenate your cells.