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

Craig Buchanan_mljjec

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.