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

Updating 2d table with 1d table data

aloysius koh March 31, 2024

confluence_help.PNG

Example of what table 1 will look like:

confluence_help_1.png

I want to pull data from Table 1 and regroup them accordingly to Table 2 so i was working my way through. Firstly, I want to pull all data from Table 1 into one element of Table 2 ("FxMotion/example_1") but it doesnt seem to be working:

UPDATE T2
SET T2.'FxMotion' = (
SELECT 'Title'
FROM T1
) WHERE 'Blocks' = "example_1";
SELECT * FROM T1

1 answer

1 accepted

3 votes
Answer accepted
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 1, 2024

Hi @aloysius koh ,

If I understand correctly, the 'Title' column consists of several parts (something like date, description, typy) that are divided by " - ". So, we can split these parts into separate columns and then aggregate them as you need.

The page structure will look as following:

Mon 6-1.png

With the help of the Table Transformer macro we split the 'Title' column:

SELECT *,
T1.'Title'->split(" - ")->0 AS 'Date',
T1.'Title'->split(" - ")->1 AS 'Description',
T1.'Title'->split(" - ")->2 AS 'Type'
FROM T*

Mon 6-2.png

Then we can aggregate this table with the help of the Pivot Table macro (I've chosen these particular settings but you can adjust them as you need):

Mon 6-3.png

Hope it helps your case.

aloysius koh April 1, 2024

For the table transformer, how do I grab the 1,2,last column of the string after it is split? Cause I noticed that I have something like this:

2024-01-03 - Example_1 - Workflow - FxClear

2024-01-03 - Example_1 - Ideas - Part 1 - FxClear

 

Another question for my "CQL Search" Macro, how do I only find title starting with '2024-01'? I tried various ways but it doesn't seem to work.  

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 1, 2024

The parts divided by " - " are numbered subsequently in the query:

T1.'Title'->split(" - ")->0 AS 'Date',
T1.'Title'->split(" - ")->1 AS 'Description',
T1.'Title'->split(" - ")->2 AS 'Type'

So, according to your new example, you have smth like this:

0 - 1 - 2 - 3 for the fist string

0 - 1 - 2 - 3 - 4 for the second string

If you have different number of such parts, then this option is not suitable for you.

I'll return here shortly with a new example for you to try.

P.S. The CQL Search macro is not ours, you may reach its vendor for further assistance.

Meanwhile I'll try to think of smth using our macros.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 1, 2024

So, you may try the following option:

Mon 7-1.png

SELECT *,
T1.'Title'->split(" - ")->0 AS 'Date',
T1.'Title'->split(" - ")->1 AS 'Description',
CASE
WHEN T1.'Title' LIKE "%FxClear"
THEN "FxClear"
WHEN T1.'Title' LIKE "%FxItem"
THEN "FxItem"
END
AS 'Item'
FROM T1
WHERE T1.'Title' LIKE "2024-01%"

Mon 7-2.png

The last string of the query helps to show only the results regarding the "2024-01..." rows.

aloysius koh April 2, 2024

It works! Thank you so much. 

For the data extracted from confluence query macro, there is no title belonging to "FxCharacter" under 'Item' but i still want a "FxCharacter" column even though it is NULL. I have wrapped the pivot macro in a table transformer macro to rearrange the column to what I want as well. 

SELECT T1.'Item',
T1.'Literal of Title FxClear' AS 'FxClear',
T1.'Literal of Title FxItem' AS 'FxItem',
T1.'Literal of Title FxCharacter' AS 'FxCharacter'
FROM T*

 

However it seems to error out. I try BEGIN TRY but I cant seem to get the syntax right. 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

If there can be or can't be a specific column but you need to refer to it in the SQL query, you may use the COALESCE function:

COALESCE('Literal of Title FxClear',"") AS 'FxClear'

aloysius koh April 2, 2024

Thanks! That works perfectly.

One last question: I got multiple entries in some of the cells of the table. How do I only get the first one per cell? 

confluence_help_2.PNG

Example: I only want "2024-01-03 - Example_1__run1 - FxItem".  The delimiter used is newline. 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

You may use the following workaround: after the Table Transformer macro that gives you a splitted table like on the screenshot below, you place another Table Transformer macro (an external one).

Tue 7-1.png

So, this Table Transformer macro is wrapped into another Table Transformer macro:

SELECT
FIRST(T1.'Title') AS 'Title',
FIRST(T1.'Date') AS 'Date',
FIRST(T1.'Description') AS 'Description',
FIRST(T1.'Item') AS 'Item'
FROM T*
GROUP BY (T1.'Description' + T1.'Item')

Tue 7-2.png

Here we select each FIRST row for each (T1.'Description' + T1.'Item') combination. 

Pay attention on the 3rd and 4th rows of the source table: the Example_2 has the same component FxClear. In the result table only the 3rd (the upper one) row is shown.

Then you can apply your Pivot Table macro as you've done before.

P.S. The LAST function for the lower rows is also supported by the Table Transformer macro.

 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

Seems that I've posted an answer but can't see it now. Let me repost it then.

As a workaround you may wrap your Table Transformer macro in which we split our table into another Table Transformer macro.

So, the output table on the screenshot below should be wrapped into another Table Transformer macro:

Tue 7-1.png

For this new Table Transformer macro you may use the following query:

SELECT
FIRST(T1.'Title') AS 'Title',
FIRST(T1.'Date') AS 'Date',
FIRST(T1.'Description') AS 'Description',
FIRST(T1.'Item') AS 'Item'
FROM T*
GROUP BY (T1.'Description' + T1.'Item')

Tue 7-2.png

Pay attention on the 3rd and 4th rows: they both have Example_2 and FxClear. But we take only the upper row.

The LAST function is also supported by the macro: it will take the lowest row.

Then you may use the Pivot Table macro as you've done before.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

Seems that my answers are not saved.

Wrap your Table Transformer macro where we split your table into another Table Transformer macro:

Tue 7-1.png

This table will be a source table for the new Table Transformer macro.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

Use the following SQL query:

SELECT
FIRST(T1.'Title') AS 'Title',
FIRST(T1.'Date') AS 'Date',
FIRST(T1.'Description') AS 'Description',
FIRST(T1.'Item') AS 'Item'
FROM T*
GROUP BY (T1.'Description' + T1.'Item')

Tue 7-2.png

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

As you can see, for the 3rd and 4th rows we had Example_2 and FxClear but only the upper (first) row was taken.

The macro also supports the LAST (lower row) function if required.

Then you may use the Pivot Table macro as you've done before.

Hope this workaround helps your case.

aloysius koh April 2, 2024

Thanks so much for your help! Yes it works as well!

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events