Hi team,
My requirement says Sum of the field or aggregate with sum function w.r.t columnA,
Raw data:
|
| |||||||||||||||
|
|
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.
How to aggregate the measure values to one dimension values
This topic has been closed for comments
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
+1
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
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.