nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Katerina Kovriga {Stiltsoft}

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.

All comments

Katerina Kovriga {Stiltsoft}

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

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}

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