Hi Dojo,
In dashboards I was wondering of it was possible to combine two tables in a query as below:
We have some amplifiers that are split into two blocks which are split over two pages in the element. The pages & tables are in the same format. The indexes used are also separate between the two tables, PAs Block 2’s table index # starts at the next PA# along from PAs Block 1’s last entry. As below, taken from the same element:
I’m currently building some dashboards that look at amplifier temperatures across our estate and I’m planning on sorting these by Amp Temp so I was wondering, as the two tables columns match up, in dashboards it is possible to combine these tables into a single table via a query rather than having to run two separate queries, one for PAs Block 1 and the other for 2 and displaying them two in separate tables on a dashboard.
Hi Chris,
I think in this case there are two possible options (that don't require updating the connector):
- Using profile definitions: You can create a profile definition that will be able to show information from both tables. Each profile parameter (linked to this profile definition) will correspond to a column that is available in both tables. For example, based on your screenshot, you will have the following profile parameters (if you would like to display in a dashboard all the columns displayed in your screenshot):
- PA
- Input Power
- Output Power
- Reflected Power
- Amp Temp
- Inlet Temp
Note: No profile instances are required.
Each profile parameter will be linked to two parameters in the connector (see linked with table in the profile parameter). For example, the profile parameter 'Input Power' will be linked to the parameter 'Input Power' from table Block1 and 'Input Power 2'Once you create the profile definition, you can create a GQI query and use the data source 'Get parameters from elements where. When selecting the type, you can select the option 'Profile Definition'. The profile definition that you created should be listed there. If you display the result of the query in a table you should see rows from both tables.Keep in mind that you will also see rows from all the elements using this connector.
- Using a custom data source: Using the GQDMS class, you can query information from the cluster and retrieve information from both tables. Using a GQI query, you can select the data source Get adhoc data and select this custom data source.
Hope it helps.
Hi Toon,
I believe an inner join will not work here since the primary key on both tables are different. From the screenshot Block 1 table contains PKs: PA1, PA2, PA3, PA4, PA5, and Block 2 table contains PKs: PA6, PA7, PA8, PA9, PA10.
An outer join will duplicate the columns (e.g. you will have two ‘Input Power’ columns)
Thanks for sharing the possible solutions. I’ll explore the options you’ve provided. Prior to posting the question I did try using the GQI Join and i can confirm that the behaviour/output is how Miguel describes.
Maybe I’m not getting the use case but aren’t GQI joins also an option here? And the most simple one?