I am having an issue with my Tableau report where group concat is not working as intended. My table uses custom SQL and grabs from a BI connector sending information from MongoDB.

Let’s say the concatonated values show as “Gerald,Simon”. It would then have a duplicate row for “Simon, Gerald” where all the dimensions are identical, but the measures in the duplicated row are all null.

Let’s say the document is part of the “Collection” (I am using placeholder names for this question), and within the document is an array called “owner”, stored in our BI connector as Collection_owner.

I have two logical tables connected via a relationship. This is due to performance issues requiring us to join on separate connections. Hard to explain but this is how it’s done here on a mass scale so please don’t focus on that element.

Table 1 is e.g.:

SELECT `Collection`.`Dimension 1`,
`Collection`.`Dimension 2`,
`Collection`.`Dimension 3`,
`Collection`.`Dimension 4`,
`Collection`.`Dimension 5`,
`Collection`.`Measure 1`,
`Collection`.`Measure 2`,
`Collection`.`Measure 3`,
`Collection`.`Measure 4`,
GROUP_CONCAT(DISTINCT `Collection_owner`.`Dimension 6`) AS `Owner`

FROM `Collection`
LEFT JOIN `Collection_owner` ON (`Collection`.`id` = `Collection_owner`.`id`)

WHERE `Collection`.`date` >= [parameters for today's date]

GROUP BY `Collection`.`Dimension 1`, `Collection`.`Dimension 2`,`Collection`.`Dimension 3`

Table 2 is:

SELECT `Collection`.`Dimension 1 (2)`,
`Collection`.`Dimension 2 (2)`,
`Collection`.`Dimension 3 (2)`,
`Collection_pet`.`Dimension1 (3)`
`Collection_pet`.`Dimension2 (3)`
`Collection_pet`.`Dimension3 (3)`

FROM `Collection`
LEFT JOIN `Collection_pet` ON (`Collection`.`id` = `Collection_pet`.`id`)

WHERE `Collection`.`date` >= [parameters for today's date]

with the relationship joined between both logical tables on Dimensions 1 2 and 3.

I have tried a WHERE clause where Measure 1 IS NOT NULL, and have added and ORDER BY clause to the Group Concat, but they do not resolve the issue.

When I drag in Tableau’s COUNT (Table) column it shows the value of 0 in these duplicate rows with blank measures, however creating a filter for at least 1 does nothing at all and still shows them

The performance is too bad to have LEFT JOINs on Collection_owner and Collection_pet in the same query

apologies, I’m new to this.