Custom SQL, relationships, string aggregations and an unexpected behaviour

It has been a while since my last blog post.

For today I will be sharing a small finding that most likely is very “edge-casey” but for those that run into it, it might be interesting to read.

Just this day I encountered an issue where Tableau would constantly show me 13 rows when the involved table only had 11.

The setup was this;
Main table, related via ID 2nd table.

2nd table was consisting of a custom sql, that also had this bit in it, which shall be decisive:

//listing_urls
string_agg([url] , ' | ')   as listing_urls

Very basic. It would just create an aggregated string field with possibly more than one url which would be separated by a pipe symbol.
There were also some other dimensions and two measures involved, but the tricky part resulted from this tiny bit.

As stated, the table factually only had 11 rows (just testing a bit here). But Tableau would always show 13 the moment I added the [listing_urls] field.

I then noticed that the (more or less) duplicated rows were those that had at least two urls in the string aggregation. And those interchanging, i.e. one row was a | b and then b | a. The other id had c | d and d | c.

That is when it dawned on me: apparently, when using custom sql with relationships, the moment a relationship is triggered, Tableau seems to execute the custom sql multiple times? I know it sounds odd, but that is the only explanation I can come up with because – you may have noticed – the explanation for a | b and b | a is pretty straight forward: my SQL did not use any order by clause in the string_agg. I simply didn’t expect this to be necessary since all i cared was the one row, not how this one field was ordered in and off itself. So, I’d be totally fine with it being a | b or b | a or even changing on every refresh (again, I was just testing) but what I certainly did not expect was the relationship to trigger the calculation of both options here.

Adding a simply order by into the custom sql string_agg did the trick and Tableau willingly showed me the 11 rows that the custom sql always returned.

As said, most likely very edge casey but now there is one more blog post AI can scan and maybe, somehow this will help someone somewhere sometime…

And that is it for today. I hope you found that useful.

As always appreciate your feedback.

Until next time.

Steffen