Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

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,465,356
Community Members
 
Community Events
176
Community Groups

Format Table - Align Cell Text with Appropriate Column Header

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!

Emily 

 

2023-01-25_11-42-58.png

1 answer

1 accepted

1 vote
Answer accepted

Sorry for the submission, I was able to solve the problem myself using the StiltSoft Advanced Use Case information.

 

I used the following SQL:

SELECT *,
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'
FROM T1

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).

Thu 5-1.png

SELECT 'Filter',
FORMATWIKI(SUM('Text' + "\\ ")) AS 'Text'
FROM (SELECT 'Text'->split("-")->0 AS 'Filter',
'Text'
FROM T*)
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:

Thu 5-2.png

Thu 5-3.png

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.

Thu 5-4.png

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.

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events