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.
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 |
@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
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
Could you share how you did the blend? I'm trying to do this exact same thing.