Updating 2d table with 1d table data

[aloysius koh]

Example of what table 1 will look like:

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

[Stiltsoft support · Accepted solution]

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:

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*

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

Hope it helps your case.

[aloysius koh]

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]

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]

So, you may try the following option:

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%"

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

[aloysius koh]

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]

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]

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? 

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

[Stiltsoft support]

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

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

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]

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:

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

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]

Seems that my answers are not saved.

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

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

[Stiltsoft support]

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

[Stiltsoft support]

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]

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