You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
Hello Community! I am tagging @Katerina Kovriga _Stiltsoft_ as she has answered several of my previous questions.
I have a table that I would like to sort the values into the appropriate column header match. See attached input and desired output tables. Any advise is appreciated!
Sorry for the submission, I was able to solve the problem myself using the StiltSoft Advanced Use Case information.
I used the following SQL:
CASE WHEN (MID(T1.'Text',1,6)) = "BPFAAA" THEN T1.'Text' END AS 'BPFAAA',
CASE WHEN (MID(T1.'Text',1,6)) = "BPFBBB" THEN T1.'Text' END AS 'BPFBBB',
CASE WHEN (MID(T1.'Text',1,6)) = "BPFCCC" THEN T1.'Text' END AS 'BPFCCC'
Hi @Emily Berg,
Glad that you’ve found an answer yourself. Let me also suggest an option if not for this case, then for the future ones – how to avoid using filters (here I mean “BPFAAA”, “BPFBBB”, etc.) in the query. It may come in handy if there are many different variants and if they are changing dynamically (for example, coming from the Jira Issues macro).
FORMATWIKI(SUM('Text' + "\\ ")) AS 'Text'
FROM (SELECT 'Text'->split("-")->0 AS 'Filter',
GROUP BY 'Filter'
Here I automatically split the first part of the ‘Text’ field (you do smth similar in your query) and group my data by these first parts.
Then I also need to transpose my result table:
And you may do the same thing using the Table Spreadsheet macro:
=IFERROR(IF(SEARCH(B$1,$A2),$A2, " "), " ")
Copy/paste this formula to all the table cells.
Unfortunately, the Table Spreadsheet macro doesn’t support the Jira Issues macro as a source table. We have such feature in our roadmap, and once it is implemented, I’ll get back here to notify you.
But if your case is based on the manually created table or csv/xlsx file, then you may also use this option.
Hello @Emily Berg ,
Let me inform you we've released a new macro - Spreadsheet from Table - which allows transferring your regular table or a table outputting a macro's content (such as, for example, Page Properties Report or Jira macro) into Table Spreadsheet with all its tools. It is available starting from Table Filter and Charts for Confluence version 10.0.0.