# Value in Pivot Field of a transform not found in source index

**URL:** <https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318>\
**Category:** Kibana\
**Created:** [June 23, 2020, 5:52pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318 "2020-06-23T17:52:57Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sanjaysubramanian](https://avatars.discourse-cdn.com/v4/letter/s/bcef8e/32.png) [@sanjaysubramanian](https://discuss.elastic.co/u/sanjaysubramanian)\
**Post date:** [June 23, 2020, 5:52pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/1 "2020-06-23T17:52:58Z")

</div>

Hi all

My transform JSON is illustrated below. A screen shot of the Transform Preview is also attached.

**TRANSFORM INDEX**  
On the Kibana Discovery Console (index = interactions\_new\_conversations\_transform\_idx) I can search for the following and get results  
`data.chat : 1699894732081160`

**ORIGINAL INDEX**  
However if I search the above ID in the original index (index = interactions), I don't see any results ! _I also queried ElasticSearch but found no results !_

Thanks  
warmly

sanjay

 ![Screen Shot 2020-06-23 at 10.47.30 AM](https://us1.discourse-cdn.com/elastic/original/3X/0/2/021671ea68ef265d449e07ec12b918213cbd2d52.png)

```auto
{
  "id": "interactions_new_conversations_transform",
  "source": {
    "index": [
      "interactions"
    ],
    "query": {
      "bool": {
        "should": [
          {
            "match_phrase": {
              "data.sendertype": "customer"
            }
          }
        ],
        "minimum_should_match": 1
      }
    }
  },
  "dest": {
    "index": "interactions_new_conversations_transform_idx"
  },
  "sync": {
    "time": {
      "field": "data.ts",
      "delay": "60s"
    }
  },
  "pivot": {
    "group_by": {
      "data.chat": {
        "histogram": {
          "field": "data.chat",
          "interval": "10"
        }
      }
    },
    "aggregations": {
      "data.ts.min": {
        "min": {
          "field": "data.ts"
        }
      }
    }
  },
  "description": "New Conversations (Chat IDs) from interactions",
  "version": "7.7.0",
  "create_time": 1592435815714
}

```

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [June 24, 2020, 7:32pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/2 "2020-06-24T19:32:19Z")

</div>

You are using a [histogram aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-histogram-aggregation.html) as `group_by`, this builds buckets in ranges of `10`:

```auto
[0,10)
[10, 20)
...

```

I guess you want to use a terms aggregation instead, your chat id is a "number" but not "numeric".

---

<div class="post-metadata">

**Author:** ![sanjaysubramanian](https://avatars.discourse-cdn.com/v4/letter/s/bcef8e/32.png) [@sanjaysubramanian](https://discuss.elastic.co/u/sanjaysubramanian)\
**Post date:** [June 24, 2020, 8:12pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/3 "2020-06-24T20:12:58Z")

</div>

Thanks @Hendrik_Muhs  
I performed two tests after I wrote this message. Would request your feedback ! Thanks

**TEST 1**  
Change the "data.chat" field data type to "keyword" instead of "long".  
This solved the issue but now I lose the "long" property of the "data.chat"

**TEST 2**  
I modified the mapping to look like this  
I am trying something else too...I am modding the mapping like this  
This way now I have the data type as integer and I also can use the **data.chat.kwrd** as an aggregation field

````auto
                  "type": "long",
                  "fields": {
                     "kwrd": {
                        "type": "keyword"
                     },
                     "txt": {
                        "type": "text"
                     }
                  }
                },```
````

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [June 24, 2020, 8:43pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/4 "2020-06-24T20:43:24Z")

</div>

Have you tried the suggested terms aggregation?

```auto
    "group_by": {
      "data.chat": {
        "terms": {
          "field": "data.chat"
        }
      }

```

Terms does not require a keyword field, but works for long fields. For every `group_by` transform takes the mapping of the source index as mapping for the destination index.

There is no need to change `data.chat` to keyword.

---

<div class="post-metadata">

**Author:** ![sanjaysubramanian](https://avatars.discourse-cdn.com/v4/letter/s/bcef8e/32.png) [@sanjaysubramanian](https://discuss.elastic.co/u/sanjaysubramanian)\
**Post date:** [June 24, 2020, 9:04pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/5 "2020-06-24T21:04:56Z")

</div>

Thanks @Hendrik_Muhs, I had not considered manual editing of the pivot specifications on the Kibana Web Console

Through the Kibana Transforms Web UI we get only one option as shown below:

 ![Screen Shot 2020-06-24 at 2.00.06 PM](https://us1.discourse-cdn.com/elastic/original/3X/f/c/fc10069a125300d4f7a1c93523b9b7db1ac42ec0.png)

```auto
========
{
  "group_by": {
    "data.chat": {
      "histogram": {
        "field": "data.chat",
        "interval": "10"
      }
    }
  },
  "aggregations": {
    "data.ts.min": {
      "min": {
        "field": "data.ts"
      }
    }
  }
}

MODIFIED THROUGH Advanced pivot editor
======================================
{
    "group_by": {
      "data.chat": {
        "terms": { <<<<<<<<<<<<< I changed this per your recommendation now :-)
          "field": "data.chat"
        }
      }
    },
    "aggregations": {
      "data.ts.min": {
        "min": {
          "field": "data.ts"
        }
      }
    }
  }

```

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [June 24, 2020, 9:09pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/6 "2020-06-24T21:09:28Z")

</div>

Ah, I see. I will let our UI developers know.

---

<div class="post-metadata">

**Author:** ![sanjaysubramanian](https://avatars.discourse-cdn.com/v4/letter/s/bcef8e/32.png) [@sanjaysubramanian](https://discuss.elastic.co/u/sanjaysubramanian)\
**Post date:** [June 24, 2020, 9:21pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/7 "2020-06-24T21:21:13Z")

</div>

Thank you @Hendrik_Muhs 🙏

---

<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:** [July 22, 2020, 9:21pm UTC](https://discuss.elastic.co/t/value-in-pivot-field-of-a-transform-not-found-in-source-index/238318/8 "2020-07-22T21:21:22Z")

</div>

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