# Finding count by distinct text row using Lens

**URL:** <https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280>\
**Category:** Kibana\
**Created:** [February 15, 2021, 7:09am UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280 "2021-02-15T07:09:09Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chetan\_Madaan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chetan_madaan/32/83908_2.png) [@Chetan\_Madaan](https://discuss.elastic.co/u/Chetan_Madaan)\
**Post date:** [February 15, 2021, 7:09am UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/1 "2021-02-15T07:09:10Z")

</div>

Hello,

I am very very new to this, Like up until 12 hours ago I hadn't even heard of elastic Search or Kibana.

I do however have basic knowledge of SQL and Other Data visualization tools (including Data Studio and others).

I am using this with Twint ([GitHub - twintproject/twint: An advanced Twitter scraping & OSINT tool written in Python that doesn't use Twitter's API, allowing you to scrape a user's followers, following, Tweets and more while evading most API limitations.](https://github.com/twintproject/twint)) trying to analyze some data.

I want to find similar tweets based on text (& record count of occurrence) but it looks like I can using text based fields in lens.

Now, I can use text bases fields in discover but it doesn't aggregate to provide me a count of the same.

Am I looking at this incorrectly?

Please assist if you can.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [February 15, 2021, 9:34am UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/2 "2021-02-15T09:34:17Z")

</div>

I'm not sure I'm fully understanding what you want to do.

> I want to find similar tweets based on text

What's "similar" here? Do you mean exactly the same text? If that's the case you can use the "Top values" function for the tweet field together with the "Count" function in Lens.

---

<div class="post-metadata">

**Author:** ![Chetan\_Madaan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chetan_madaan/32/83908_2.png) [@Chetan\_Madaan](https://discuss.elastic.co/u/Chetan_Madaan)\
**Post date:** [February 16, 2021, 8:02pm UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/3 "2021-02-16T20:02:35Z")

</div>

Hi,

Yes, "exact text match". But the problem is the tweet field is not visible in lens.

It is however visible in Discovery. Do I need to create some sort of index or something?

---

<div class="post-metadata">

**Author:** ![Chetan\_Madaan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chetan_madaan/32/83908_2.png) [@Chetan\_Madaan](https://discuss.elastic.co/u/Chetan_Madaan)\
**Post date:** [February 16, 2021, 8:05pm UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/4 "2021-02-16T20:05:15Z")

</div>

Adding more to it.

The available fields in discover are 37 but in less there are only 19 (see screenshots below).

 ![discover](https://us1.discourse-cdn.com/elastic/original/3X/9/6/96a80708480d093c1bc7f9873d33acfce179c66b.jpeg)

 ![lens](https://us1.discourse-cdn.com/elastic/original/3X/b/c/bcea467f914e5d30092aacbdd6c3af8e59b7b654.jpeg)

Thanks,  
Chetan

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [February 17, 2021, 8:10am UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/5 "2021-02-17T08:10:50Z")

</div>

OK, so the difference between the Discover and Lens views are Discover working on individual documents while Lens works with aggregated data only.

Depending on how your index is configured, not all fields are aggregatable. For Discover this doesn't matter - it can still show all of them. But in Lens, the list of fields is pre-filtered.

If you go to Management \> Index patterns you can see this:

 ![Screenshot 2021-02-17 at 09.06.34](https://us1.discourse-cdn.com/elastic/original/3X/f/8/f87558b219b54b240048bec3371f83fc86e546cd.png)

`agent.keyword` is usable in Lens and will show up in the list, while `agent` is not. In Discover, you can use both fields.

If the tweet message field shows up in Discover but not in Lens it means it's not aggregatable. To fix this, you have to change your mapping and make sure it's indexed as "keyword" (not only text). Then you need to re-index your existing data so the aggregatable index is built within Elasticsearch.

> **[Keyword type family | Elasticsearch Reference \[7.11\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html#keyword-field-type)**

> **[Reindex API | Elasticsearch Reference \[7.11\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-reindex.html)**

A common mapping to use (like in the screenshot above) is to have the original field indexed as "text" for full text search with a second field suffixed with ".keyword" for the keyword indexed version of the same data for aggregations. This gives you the best of both worlds:

```auto
        "agent" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }

```

---

<div class="post-metadata">

**Author:** ![Chetan\_Madaan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chetan_madaan/32/83908_2.png) [@Chetan\_Madaan](https://discuss.elastic.co/u/Chetan_Madaan)\
**Post date:** [February 17, 2021, 2:41pm UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/6 "2021-02-17T14:41:19Z")

</div>

Thanks @flash1293, this helps understand the poroblem. Yes, the field is not aggregatable in the index.

I am trying to do the above based on the code you provided but that doesn't seem to be working. I am positive I am missing something here.

 ![console-kibana](https://us1.discourse-cdn.com/elastic/original/3X/6/2/626af79645d3eda808b3c195476c12d7d47ebe25.jpeg)

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [February 17, 2021, 2:53pm UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/7 "2021-02-17T14:53:06Z")

</div>

This tries to set the mapping for the tweet type, not the tweet field. I think the best approach is to create a completely new index mapping (maybe `twinttweets_fixed`) with all fields, including the keyword version of the `tweet` field, then use the re-index api to shovel the data from `twinttweets` to `twinttweets_fixed`.

---

<div class="post-metadata">

**Author:** ![Chetan\_Madaan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chetan_madaan/32/83908_2.png) [@Chetan\_Madaan](https://discuss.elastic.co/u/Chetan_Madaan)\
**Post date:** [February 17, 2021, 3:09pm UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/8 "2021-02-17T15:09:18Z")

</div>

That's a lot of learning for me right there, I'll go about doing that. Thank you @flash1293

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 17, 2021, 3:09pm UTC](https://discuss.elastic.co/t/finding-count-by-distinct-text-row-using-lens/264280/9 "2021-03-17T15:09:35Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.