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,293,370
Community Members
 
Community Events
165
Community Groups

Merge tables from table transformer macro

Edited

Hi all, 

im struggling to create a merge 2 tables where 1 of them is a result of a table transformer macro with the goal of getting automatic updates and avoid any manual intervention. See below the case:

1. Fixed table

  • Screenshot 2022-06-09 at 09.54.57.png

 

2. Macro table ("KPI" data)

  • Screenshot 2022-06-09 at 09.56.44.png

 

  • Screenshot 2022-06-09 at 09.58.11.png

 

I've tried adding both tables to a new table transformer macro where do the merge (using default SQL config), this is the actual result:

  • Screenshot 2022-06-09 at 10.02.08.png
  • Screenshot 2022-06-09 at 10.02.55.png
  • Screenshot 2022-06-09 at 10.05.03.png

 

Desired result

  • Screenshot 2022-06-09 at 10.00.08.png

where "100" is somehow automatically fetched from the table transformer macro. Each month will have a specific table transformer result so the "Response Times" column name should be changed to "01", "02", ... to do the proper mapping, just did it this way for example purposes.

I assume it's about SQL code to the the proper join and columns mapping, but were not able to find a workable solution for this so far.

Thanks in advance for your attention and help!

Regards,

Esteban

 

 

 

1 answer

1 accepted

Hi @Esteban Borja Lopez ,

These two tables can't be merged together in a proper way. The first Merge preset that you choose merges the tables with similar headers:

|Col 1 |Col 2 |

|A       |12     |

|B       |14     |

+

|Col 1 |Col 2 |

|C       |23     |

|D       |44     |

=

|Col 1 |Col 2 |

|A       |12     |

|B       |14     |

|C       |23     |

|D       |44     |

The second Look Up preset merges tables by the unique column.

For example, in your case it's Month.

|Month |Col 2             |

|01        |Some info 01|

|02        |Some info 02|

|03        |Some info 02|

|04        |Some info 02|

So to add the KPI you need to generate one big or several small tables with the format:

|Month |KPI |

|02        |100|

Then the result will be as following:

|Month |Col 2             |KPI |

|01        |Some info 01|      |

|02        |Some info 02|100|

|03        |Some info 02|      |

|04        |Some info 02|      |

Hope I was able to clarify the case a little bit for you.

Hi @Katerina Kovriga _Stiltsoft_ , thanks a lot for your response, always a pleasure seeing your name involved :)

I've modified the macro SQL to generate same header as the fixed table

Screenshot 2022-06-09 at 11.15.17.png

Tried adding an additional macro for month 02 and set the following SQL on the main table transformer

SELECT
*
FROM  T1 JOIN T2 ON T1.'Month' = T2.'Month'

 

Result

Screenshot 2022-06-09 at 11.22.18.png

so it's working for 1 table but I would need to tune the SQL to map T1.'Month' to each 'Month' column from the additional 11 tables... any hint on how to modify above SQL to do something like "FROM  T1 JOIN T* ON T1.'Month' = T*.'Month'"?

Regards,

It seems that the case is more simple - don't change anything in the SQL query and just choose the Look Up preset.

Here is my example with one stable table (and it doesn't require the KPI column, the Month column is enough) and three mini-tables (let's pretend that they are generated by other internal Table Transformers):

Thu 3-1.png

Then I choose the Look Up preset and the common Month column is shown for me to choose by default:

Thu 3-2.png

And here is the result:

Thu 3-3.png

Like Esteban Borja Lopez likes this

It worked like a charm:

Screenshot 2022-06-09 at 12.28.22.png

And as for my final goal as well:

Screenshot 2022-06-09 at 12.35.06.png

So all working as desired and no manual work other than initial config.

You rock @Katerina Kovriga _Stiltsoft_ :)

Thank you!

I'll pass your feedback about the app to the team. :)

Like Esteban Borja Lopez likes this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

733 views 5 21
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you