How can i creating derived table from 3 different looks

Hi All

I have a situation that i need your suggestions for solution,
Problem:
I need to collect data from 3 Looks into one look (which of course can be done by merge) but I need to create a derived table based on the latter Look.
The reason for creating a derived table is that I need to use it to link it to other queries.

The Exact situation:

  • I have 3 different project Types in which i can allocate a running fleet of trucks. So each truck can work in [Rental Project], [Trading Project] or [Other Project].
  • The data of Each project type is collected in a separate Database table.
  • I created 3 views to display the Total Km traveled for each type of these project.
  • I can join the 3 Looks by using merge and get the total Km traveled within specific month for each truck. Yet I can not create a derived table from a merged look.
  • each table looks exactly like below:

The need:
I want to create a derived table out of the results of the 3 created looks to be able to use it in another queries.

I appreciate so much you valuable contribution,

Sincerely
Hisham

Hi @Hisham_Elhadidi,

Assuming the three project tables are in the same database, but not the same tables, why don’t you UNION the three project tables in one view, as a derived table, and then just use that one view? Then there is no need to merge right?

Cheers,

Jon

2 Likes

Hi @Jonathan_Palmer

The Total KM of each table is an aggregate function that SUM the individual records over certain time period.
Hence, when linking the 3 tables (that contains the 3 types of projects) with the Fleet Data table (that contains the Trucks Logos - Left outer joint with many to one relationship) , the SUM aggregate function doesn’t work properly.
Also the date range of each table needs to be set individually to reflect the Total Km within the same interval - Which cannot work while joining the 3 tables.

What do you think?

Sincerely
Hisham

I would go with the union method suggested, adding a source column for which of your three tables it comes from:

view: projects {
  derived_table: {
    sql:
      with

        rental_project as (
              select 
                *,
                rental as source
              from
                [Rental Project]
        ),

        trading_project as (
              select 
                *,
                trading as source
              from
                [Trading Project]
        ),

        other_project as (
              select 
                *,
                other as source
              from
                [Other Project]
        )

select * from rental_project
    union all
select * from trading_project
    union all
select * from other_project
;;

  }

  dimension: truck_logo {
    type: number
    sql: ${TABLE}.truck_logo ;;
  }

  ...etc

}
1 Like