Merge tables from table transformer macro

[Esteban Borja Lopez]

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

• 

2. Macro table ("KPI" data)

• 

• 

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:

• 
• 
• 

Desired result

• 

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

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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]

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

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

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}]

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

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

And here is the result:

[Esteban Borja Lopez]

It worked like a charm:

And as for my final goal as well:

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

You rock @Katerina Kovriga {Stiltsoft} :)

[Katerina Kovriga {Stiltsoft}]

Thank you!

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