# Very complex filter (like group by)?

**URL:** <https://discuss.elastic.co/t/very-complex-filter-like-group-by/311585>\
**Category:** Kibana\
**Created:** [August 6, 2022, 6:36pm UTC](https://discuss.elastic.co/t/very-complex-filter-like-group-by/311585 "2022-08-06T18:36:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ely\_96](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ely_96/32/103816_2.png) [@Ely\_96](https://discuss.elastic.co/u/Ely_96)\
**Post date:** [August 6, 2022, 6:36pm UTC](https://discuss.elastic.co/t/very-complex-filter-like-group-by/311585/1 "2022-08-06T18:36:15Z")

</div>

Hi Guys,

I need an help because I cannot find a way to filter a simple datatable.

My index pattern consists of the following csv:

```auto
ID;Class;User Age
1234;A;18
1235;A;23
1236;B;25

```

I would like to set a filter in the top left bar to see in my datatable only the classes (second column of the CSV) that have **all**"User Age"\> 20. So in my example I should only see class B.

If I set a filter like:  
User Age \> 20 I can see both ID: 1235 and 1236 but I don't want to see ID 1235 because class A has a User Age \<20 (ID 1234).

Could anyone please help me?

Thanks in advance  
Ely

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [August 7, 2022, 11:26pm UTC](https://discuss.elastic.co/t/very-complex-filter-like-group-by/311585/2 "2022-08-07T23:26:50Z")

</div>

Hi @Ely_96 Yup its a little harder and you would use a PIvot Transform and then you can do what you want.

You can look up the docs [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) there are samples and everything

And you can just dive in... and try it through Kibana -\> Stack Management -\> Transform

Below is a complete solution through the API with some assumptions  
You will need to create a Data View for each of the initial index and the resulting transform index.

Initial Class Data

```auto
DELETE discuss-class
PUT discuss-class
{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "class": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }
}

POST discuss-class/_bulk
{ "index":{ } }
{ "id" : "1234", "class" : "101", "age" : 18}
{ "index":{ } }
{ "id" : "1235", "class" : "101", "age" : 21}
{ "index":{ } }
{ "id" : "1236", "class" : "201", "age" : 24}
{ "index":{ } }
{ "id" : "1237", "class" : "201", "age" : 35}
{ "index":{ } }
{ "id" : "1238", "class" : "301", "age" : 24}
{ "index":{ } }
{ "id" : "1239", "class" : "301", "age" : 25}
{ "index":{ } }
{ "id" : "1240", "class" : "301", "age" : 25}
{ "index":{ } }
{ "id" : "1241", "class" : "301", "age" : 36}
{ "index":{ } }
{ "id" : "1242", "class" : "301", "age" : 20}
{ "index":{ } }
{ "id" : "1243", "class" : "102", "age" : 25}
{ "index":{ } }
{ "id" : "1244", "class" : "102", "age" : 36}
{ "index":{ } }
{ "id" : "1224", "class" : "102", "age" : 18}

```

Now the transform

The transform destination index

```auto
DELETE discuss-class-transform-index

GET discuss-class-transform-index

PUT discuss-class-transform-index
{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "id_value_count": {
        "type": "long"
      },
      "class": {
        "type": "keyword"
      },
      "age_min": {
        "type": "integer"
      },
      "age_max": {
        "type": "integer"
      }
    }
  }
}

```

Now the transform ... again please feel free to go to the GUI and do the same... careful with the field names. Note I did min and max age you could do average etc...

```auto
DELETE _transform/discuss-class-transform

GET _transform/discuss-class-transform

PUT _transform/discuss-class-transform
{
  "source": {
    "index": [
      "discuss-class"
    ],
    "query": {
      "match_all": {}
    }
  },
  "dest": {
    "index": "discuss-class-transform-index"
  },
  "pivot": {
    "group_by": {
      "class": {
        "terms": {
          "field": "class"
        }
      }
    },
    "aggregations": {
      "age_max": {
        "max": {
          "field": "age"
        }
      },
      "id_value_count": {
        "value_count": {
          "field": "id"
        }
      },
      "age_min": {
        "min": {
          "field": "age"
        }
      }
    }
  },
  "settings": {}
}

```

**Now Key** you have to start / run the transform

```auto
POST _transform/discuss-class-transform/_start

```

Now the data is there you can do a quick search if you want but create a Data View

```auto
GET discuss-class-transform-index/_search

```

 ![Screen Shot 2022-08-07 at 5.19.00 PM](https://us1.discourse-cdn.com/elastic/original/3X/3/5/352bd4fed3f5a23ceff6c0d6cf9c3f75dc70c5e8.png)

And now you can do all sorts if easy stuff

Now All the Classes

 ![Screen Shot 2022-08-07 at 5.22.53 PM](https://us1.discourse-cdn.com/elastic/original/3X/e/b/eb18f704145881cb94e7cdd034b6da194ebf0547.png)

Now classes with min Age \>= 20

The Filter

 ![Screen Shot 2022-08-07 at 5.25.28 PM](https://us1.discourse-cdn.com/elastic/original/3X/c/6/c6042ae9a64c637a0b4b756fd4b86480479f67c5.png)

The Result

 ![Screen Shot 2022-08-07 at 5.25.45 PM](https://us1.discourse-cdn.com/elastic/original/3X/c/f/cf55809b5eb077fee53d17febd5d1daff0e2e26e.png)

---

<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:** [September 4, 2022, 11:26pm UTC](https://discuss.elastic.co/t/very-complex-filter-like-group-by/311585/3 "2022-09-04T23:26:55Z")

</div>

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