Merge tables from table transformer macro

Esteban Borja Lopez June 9, 2022

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

3 votes
Answer accepted
Katerina Kovriga {Stiltsoft}
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.
June 9, 2022

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.

Esteban Borja Lopez June 9, 2022

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,

Katerina Kovriga {Stiltsoft}
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.
June 9, 2022

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
Esteban Borja Lopez June 9, 2022

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

Katerina Kovriga {Stiltsoft}
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.
June 9, 2022

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
AUG Leaders

Atlassian Community Events