Question

How to aggregate the measure values to one dimension values

  • 20 December 2021
  • 6 replies
  • 303 views

Hi team,

 

My requirement says Sum of the field or  aggregate with sum function w.r.t columnA, 

Raw data:

 

ColumnA ColumnB  
A U 10
A V 10
B X 20
B Y 20

 

 

 

 

Required table:

ColumnA ColumnB Measure
A U 20
A V
B X 40
B Y

 

So, I want to group by Field on column A.


This topic has been closed for comments

6 replies

Hi,

I can only think of this solution. Hope it helps.

view: example2 {
  derived_table: {
    sql: select
    ColumnA,
    ColumnB,
    ColumnC,
    sum(ColumnC) OVER (PARTITION BY ColumnA) AS result
    from example ;;
  }

  dimension: ColumnA {}
  dimension: ColumnB {}
  dimension: ColumnC {}
  dimension: result {}
}

 

The simplest way is just use Column A (without ColumnB) to get the result, but I guess that’s not what you want to present in the table.

Jack

Jack,

Is there any other way to create Example2 Result without Derived table? Can we use table calculation of LookML?

I tried your suggestion but it is returning this error:

 

If you like, you can do this

 

WITH

  temp AS (

  SELECT

    DISTINCT ColumnA,

    SUM(ColumnC) AS result

  FROM

    example

  GROUP BY

    ColumnA ),

  fact AS (

  SELECT

    ex.*,

    ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY ColumnA) AS rn,

  FROM

    example ex 

SELECT

  fact.ColumnA,

  fact.ColumnB,

  fact.ColumnC,

  temp.result

FROM

  fact

LEFT JOIN

  temp

ON

  fact.ColumnA = temp.ColumnA

  AND fact.rn = 1

 

Row ColumnA ColumnB ColumnC result  
1

A

U

10

20

 
2

A

V

10

null  
3

B

X

20

40

 
4

B

Y

20

null
Userlevel 7
Badge +1

@rohit.shoppertrak you can’t “make” window functions in Table Calculations or even sql parameter in LookML, it has to be either pre-aggregated in your data model or done with a derived table

I am surprised that a group by clause is so difficult to create here. I have achieved it the required table by merge tables concept.

 

Thanks,

Rohit