How do I count duplicate values in a table?

(Wayne Henry) #1

I’m looking to count (and show the count) for duplicate values in a table, similar to how in Excel I can use the @ symbol to refer to a cell in the current row, then count how many times that row appears in the column, i.e. COUNTIF([CustomerName],[@CustomerName])

How do I replicate this in looker, using table calculations?

(sara.leon) #2

Hi Wayne!

One way to do this using table calculations is to wrap a sum function around an if statement. An example of this is:


Here I’ve got a city name (type string), Woodridge, that I’m using boolean logic on. I’m then summing all the 1’s that occur, so all the instances where the city name is Woodridge.

Hope that helps!

Sara L.

(Wayne Henry) #3

Hey Sara!

Thanks a lot for your response, which would be useful in some circumstances, but as I’m dealing with 1000s of rows, I was after something similar but dynamic, so in other words, I wouldn’t have to specify “Woodridge” but rather “count how many times the specified cell in this row appears in the specified column”, so going down the column, the formula counts occurrences of “Woodridge”, “Libertyville”, “Richmond” etc, without me having to specify for each one. Does that make sense and do you know of a way to do that?

Thanks in advance!

(Sara Guzman) #4

Hi Henry,

We can count the Frequency of a Value in a Column by, modifying the table calcs for subtotals described here then, we can use the table calc below:

count(${view.field}) - match(${view.field}, offset(${view.field}, count(${view.field}) - row() * 2 + 1)) + 2

match(${view.field}}, ${view.field})

Please let us know if that helps!

(Wayne Henry) #5

Hi Sara!

My apologies for the delay in replying to your post, due to being on vacation. Thanks a lot for that calculation, it was exactly, what I was looking for and actually opened a lot more possibilities. Thanks a lot!