Magic ETL

Magic ETL

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

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.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • Domo Employee
    Answer ✓

    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.

    Randall Oveson <randall.oveson@domo.com>

Answers

  • 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! **

  • Domo Employee
    Answer ✓

    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.

    Randall Oveson <randall.oveson@domo.com>

  • 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!

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In