How to aggregate the measure values to one dimension values

[rohit_shoppertr]

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.

[JackLin]

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

[rohit_shoppertr]

Jack,

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

[rohit_shoppertr]

I tried your suggestion but it is returning this error:

[JackLin]

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

[Dawid]

@rohit_shoppertr 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

[rohit_shoppertr]

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

[nlegair]

Could you share how you did the blend? I'm trying to do this exact same thing.