Group By Tile to JOIN Tile Produces Unexpected Result. Using an Output Tile as an Input Fixes It

M_D_23 · 2024-08-16T15:27:35+00:00

There was an error rendering this rich post.

M_D_23

Greetings,

I have aggregated transaction data using the Group By tile to produce the following schema:

Project ID (GROUP)

Amount (SUM)

Labor Cost (SUM)

I then join this dataset to a list of transactions which are also grouped by Project ID.

Left table = = Transaction dataset

Right table = = Grouped dataset from above

Joined on Project ID

Data types all match

When I preview the JOIN tile, I see only a fraction of the Amount (SUM) value from the above dataset for any given Project ID. However, if I connect the Group By tile to an Output tile and then select the resulting dataset as an Input tile for the JOIN, I see the expected Amount (SUM) value.

Any insights or thoughts appreciated from this great community. Thank you in advance.

Find more posts tagged with

Accepted answers

rco

Previews operate on a limited number of rows from their inputs, by default just 10k rows:

This can cause Group By aggregates to look incomplete, Joins to miss when they should hit, and Filters to produce zero rows when they should produce some.

All comments

ArborRose

It sounds like you are encountering different levels of aggregation - the left table and the right table aren't matching correctly. Make sure the tables are aggregated consistently, and that the data formatting match.

If there are duplicate 'Project ID' values in the left table and not in the right table, you will get unexpected results. Make sure you are using the correct type of join (e.g. LEFT JOIN).

rco

Previews operate on a limited number of rows from their inputs, by default just 10k rows:

This can cause Group By aggregates to look incomplete, Joins to miss when they should hit, and Filters to produce zero rows when they should produce some.

M_D_23

Thank you @ArborRose. I appreciate your response!

Thank you @rco - this seems to have been the issue. I really appreciate you pointing this out to me. What a neat feature!

Thanks again to both respondents!