How can i creating derived table from 3 different looks

Hi All

I have a situation that i need your suggestions for solution,
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,


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?




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?


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: {

        rental_project as (
                rental as source
                [Rental Project]

        trading_project as (
                trading as source
                [Trading Project]

        other_project as (
                other as source
                [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 ;;


1 Like