Question

Duplicate rows in pivoted table

  • 27 June 2018
  • 3 replies
  • 1705 views

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 (

SELECT

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

) aa

) xx

) zz

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:



3 replies

Userlevel 2

Hi Holger,



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.



Many Thanks,


Bernard

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. "

Userlevel 2

Great job! Thanks for updating us, if you need any further help don’t hesitate to ask!



B

Reply