We set up an Explore with two dimension (one as rows, one as columns/pivot) and one measure. When the output is being shown in Looker, we see multiple rows for one and the same element of the dimension:
This is some very unexpected behaviour that also leads to error messages (“Unable to chart pivoted data when dimension Marketing Controlling Partner has repeated values. This commonly happens when you create a query with a second dimension and hide that dimension from the chart.”).
That’s the SQL that Looker creates:
SELECT * FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY z___min_rank) as z___pivot_row_rank, RANK() OVER (PARTITION BY z__pivot_col_rank ORDER BY z___min_rank) as z__pivot_col_ordering FROM (
SELECT *, MIN(z___rank) OVER (PARTITION BY "marketing_controlling.partner") as z___min_rank FROM (
SELECT *, RANK() OVER (ORDER BY "marketing_controlling.partner" ASC, z__pivot_col_rank) AS z___rank FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY "marketing_controlling.date_month" NULLS LAST) AS z__pivot_col_rank FROM (
upper(marketing_controlling.partner) AS "marketing_controlling.partner",
TO_CHAR(DATE_TRUNC('month', marketing_controlling.date ), 'YYYY-MM') AS "marketing_controlling.date_month",
COALESCE(SUM(case when marketing_controlling.measure like '%cost%' then coalesce( marketing_controlling.actual, 0 ) else 0 end), 0) AS "marketing_controlling.cost_actual"
FROM fact.marketing_controlling AS marketing_controlling
WHERE (marketing_controlling.date >= TIMESTAMP '2018-04-01') AND (((upper(marketing_controlling.partner)) = 'STROER'))
GROUP BY 1,DATE_TRUNC('month', marketing_controlling.date )) ww
) bb WHERE z__pivot_col_rank <= 16384
WHERE z___pivot_row_rank <= 500 OR z__pivot_col_ordering = 1 ORDER BY z___pivot_row_rank
and this is the result from the SQL runner:
Thanks a million for reaching out. Could you email further details, and a link to the explore to help.looker.com and we will get right on the investigation.
Solved: "After some tests, I found out that the TRIM function is what we needed to add to the dimension: partner (see function here), as sometimes there are spaces or blanks in the fields type: string. As you can see in this explore, the measure does not get divided into two, please this explore. "
Great job! Thanks for updating us, if you need any further help don’t hesitate to ask!