1 Reply Latest reply on Jul 9, 2018 7:33 PM by Alex Kerin

    Joining/filtering multiple 1-to-many joined tables

    RangaWal

      I have the following data in Excel spreadsheets:

      • Parent table
        • Projects Table - single row for each project, contains a unique project number and many dimensions I want to use to filter, many of which do not existing in the child tables.
      • Child Tables
        • Multiple other tables containing a project number field, that are all 1-to-many linked by the unique project number

       

      So, all the child tables have a one-to-many relationship with the Projects Table with the project number as the linking field. The problem I'm trying to solve, is when joining all the tables, the number of rows grows exponentially. This also means with each new table I join, rows are duplicated. For example, the projects table is about 1000 rows. The first table I joined contains about 70,000 rows, so results in 70,000 records returned. The second table I joined has about 5000 rows, and results in about 670,000 records returned. The third table I joined contains about 700 rows, and the resulting record set is over 168 million rows! You can see my dilemma.

       

      I've looked at data blending, but believe filtering across all tables using dimensions that only exist in the parent table is not possible.  Must the filter dimension exist in each table?

       

      Am I doing something fundamentally wrong? I figure this must be a common data scenario, and therefore believe there must be a straight-forward solution to join and filter the data.