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

Options
M_D_23
M_D_23 Member
in Magic ETL

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.

Best Answer

  • rco
    rco Contributor
    Answer ✓

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

    image.png


    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.

    Randall Oveson <randall.oveson@domo.com>

Answers

  • ArborRose
    ArborRose Coach

    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).

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • rco
    rco Contributor
    Answer ✓

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

    image.png


    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.

    Randall Oveson <randall.oveson@domo.com>

  • M_D_23
    M_D_23 Member

    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!

Categories