# Display counts of values that occur n times in Table?

Let’s say I have this query in loki

``````{tenant_id="tenantId"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`
| pattern `<_> name=<name> `
| label_format name=`{{ .name }}`
``````

I want to have the data presented in a Table. When I Transform the data, I can do a Group By (date) and I can go to Calculate → All Values and then I can see all Values i.e.

``````[ "2024-05-14", "2024-05-14", "2024-05-14", "2024-05-13"]
``````

Now I want to have a count, where if the same value occurs 3 times, increment it by one. so for the example above, the count will 1 one

For the example below the count will be 2

``````[ "2024-05-14", "2024-05-14", "2024-05-14", "2024-05-13", "2024-05-12", "2024-05-12", "2024-05-12"]
``````

Hi eggy,

what ever you do with loki,
after that you have to transform the data.

first - “Extract fields” by labels
then - “Filter fields by name”
then - “Convert the field type” because from loki you get only text

then you can give us an exacly example whats in the table view.

Jo

group by

So for instance, I’d like another column (let’s say called fail count), that contains the logic "if a date (string, even) is repeated 3 times, increment the count by 1)

so

``````["2024-05-15","2024-05-15","2024-05-15"]
``````

would produce 1.

but

``````
["2024-05-15"]
``````

would produce 0 .

however

``````["2024-05-15","2024-05-15","2024-05-15", "2024-05-14","2024-05-14","2024-05-14"]
``````

would produce 2 and so on

It confuses me a little bit that it is always the same date.
How am I supposed to assign a count?

I am searching for the key…

This is the raw query

``````{tenant_id="tenantId", cluster_name="clusterName"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`

``````

date is a label_format

check this out here for some grouping syntax

try to do your aggregation in the data source and leave the visualization to grafana is my motto

If i aggregate the data with Loki as such

`````` count by (date, websiteId) (
count_over_time(
{tenant_id="tenantId", cluster_name="clusterName"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`
[\$__range]
)
) > 2
``````

I am unable to see the data in Transform when I try to extract the values

well you might not have any data ` > 2`

what do you see when you do

``````count by (date, websiteId) (
count_over_time(
{tenant_id="tenantId", cluster_name="clusterName"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`
[\$__range]
)
)
``````

show us the data view

There are matches but the ‘label {}’ are not coming up in the ‘source’ when i extract fields.

If i exclude the ‘count by’ and the ‘count_over_time’ I am able to see the ‘label {}’ in the Source

Ok i managed to do it.

I was displaying the values as part of a range Type instead of an instant .

I also added all the values I wanted to see in the table to the count by (date, websiteId) so I could see it in the table.

With regards to the count I done a transformation, in the group by i set the failures Calculate as Count.

Thanks all

1 Like