Table Transformer - how do you get the WHERE x IN function to work?

[Craig Buchanan]

I am trying to get the WHERE x IN function to work with no luck in the table transformer so would love some help in getting this to work where multiple conditions are required

Example:

Table 1

Table 2

SQL Query

SELECT
T2.'Name',
T2.'Team ref',
T2.'Location',
T2.'Allocation',
NC.'Public Holidays',
T2.'Annual Leave'
FROM
(SELECT 'Location', COUNT('Location') AS 'Public Holidays' FROM T1 GROUP BY T1.'Location') NC
LEFT JOIN T2
ON T2.'Location'=NC.'Location'
WHERE 'Team ref' IN ("Apple", "Common")

Results in:

I have also tried changing the WHERE statement to be:

WHERE 'Team ref' IN ('Apple', 'Common')

With the same result

[Katerina Kovriga _Stiltsoft_]

Hi @Craig Buchanan ,

Please clarify which hosting do you use - Cloud or Server/DC (and the app version for the latter).

I've checked your query both on the Cloud version and the up-to-date Server version and everything seems to work fine:

I've copied your query without any changes:

If you use the advanced cells formatting, you'll be able to align the Public Holidays column text to the left as well:

SELECT
T2.'Name',
T2.'Team ref',
T2.'Location',
T2.'Allocation',
FORMATWIKI("{cell:align=left}" + NC.'Public Holidays' + "{cell}") AS 'Public Holidays',
T2.'Annual Leave'
FROM
(SELECT 'Location', COUNT('Location') AS 'Public Holidays' FROM T1 GROUP BY T1.'Location') NC
LEFT JOIN T2
ON T2.'Location'=NC.'Location'
WHERE 'Team ref' IN ("Apple", "Common")

So, you may also try to recreate you tables on another Confluence page and wrap them into a fresh Table Transformer macro - maybe it's kind of a temporary bug related to some cache or smth.

[Craig Buchanan]

@Katerina Kovriga _Stiltsoft_  I have tried again in a new confluence page and even in a new space and still the same issue.

We are using server and version is Table Filter and Charts for Confluence plugin and version is 6.3.1

[Katerina Kovriga _Stiltsoft_]

Hi @Craig Buchanan ,

I've consulted the developers team and they strongly recommend to update the app to make the query work (it should work from the 6.3.8 version).

The current version is 7.1.9.

Besides from the 7.0.0 version you'll be able to use Smart Charts Insertion in the page view mode.