# Aggregate the data such that there's a new column which depends on the original Question?
**URL:** https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859
**Category:** Kibana
**Created:** [January 12, 2021, 3:55pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859 "2021-01-12T15:55:10Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Prabhu.292](https://discuss.elastic.co/u/Prabhu.292)
#### Post date: [January 12, 2021, 3:55pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/1 "2021-01-12T15:55:11Z")
Dear Team,
Below is my requirement.
I have 2 fields in my system (1. Category & 2. Questions)
Here is my requirement goes. and below is my data.
My requirement. I need to Manually add one more new field called **Sub category** by writing elastic search query which is not there in the system. with the below criteria.
1. If my **question** is "I need to get parcel as soon as possible" AND **Category** is "Expediate" Then it should be "Express" as subcategory
2. If my **question** is "I need parcel after 2 days" AND **Category** is "Medium" Then it should be "MIDLEVEL" as subcategory
3.
1. If my **question** is "I need today itself " AND **Category** is "Super fast " Then it should be "HIGHER" as subcategory
Below should be my output
1. Need Questions field aggregation
2. Need New field as subcategory aggregation
Expected result : aggregate the data such that there's a new column which depends on the original `Question`
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [January 13, 2021, 10:58am UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/2 "2021-01-13T10:58:08Z")
Hey, it sounds like this is a good use case for scripted fields: [https://www.elastic.co/guide/en/kibana/current/scripted-fields.html](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html)
You can put the custom logic you noted down in your question into painless code and return the appropriate subcategory string, then add it as a scripted field named "subcategory" in the index pattern. Once you've done that, you can use it like any other field (e.g. to do a terms aggregation on it)
---
### Author:  [@Prabhu.292](https://discuss.elastic.co/u/Prabhu.292)
#### Post date: [January 13, 2021, 11:47am UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/3 "2021-01-13T11:47:43Z")
Hi Joe,
This will not solve my issue.
My requirement is I have some around 50k questions in that need to pick up only 10 questions which is static.
For that 10 Questions I need to show the 3 sub category which is FIXED (EXPRESS , MIDLEVEL, HIGHER).
How to write a elastic search query to get only 10 questions along with the 3 sub category
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [January 13, 2021, 12:14pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/4 "2021-01-13T12:14:22Z")
The scripted field can also return null if the current document is not relevant. Then it won't show up if you use it in a terms aggregation
---
### Author:  [@Prabhu.292](https://discuss.elastic.co/u/Prabhu.292)
#### Post date: [January 13, 2021, 12:41pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/5 "2021-01-13T12:41:31Z")
Would you mind sharing one example for scripted field that to put static 10 questions
I tried this below query: but not working
if (doc['question'].value == 'I need to get parcel as soon as possible','Parcel required as soon as possible') AND Category == 'Expediate'{
return 'Express';
}else if (doc['question'].value == 'I need parcel after 2 days','Parcel required after 2 days') AND Category ==' Medium' {
return 'MIDLEVEL';
}else if (doc['question'].value == 'I need today itself','parcel required today at any cost') AND Category ==' HIGHER' {
return 'MIDLEVEL';
}
---
### Author:  [@Prabhu.292](https://discuss.elastic.co/u/Prabhu.292)
#### Post date: [January 18, 2021, 2:29pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/6 "2021-01-18T14:29:34Z")
Dear Team, Can some one help me with this. Facing challenges
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [January 18, 2021, 2:40pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/7 "2021-01-18T14:40:45Z")
What exactly isn't working? You can use "Get help with the syntax and preview the results of your script." link to get a preview for 10 documents and the return value. This can be helpful to debug your script whether it's doing the right thing.
---
### Author:  [@Prabhu.292](https://discuss.elastic.co/u/Prabhu.292)
#### Post date: [January 18, 2021, 3:56pm UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/8 "2021-01-18T15:56:23Z")
I Ran below statement:
if (doc['question.keyword'].value == 'I need to get parcel as soon as possible','Parcel required as soon as possible') AND Category == 'Expediate'{
return 'Express';
}else if (doc['question.keyword'].value == 'I need parcel after 2 days','Parcel required after 2 days') AND Category ==' Medium' {
return 'MIDLEVEL';
}else if (doc['question.keyword'].value == 'I need today itself','parcel required today at any cost') AND Category ==' HIGHER' {
return 'MIDLEVEL';
}
I am getting the following error. Do no how to write correct query format.
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [January 19, 2021, 9:35am UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/9 "2021-01-19T09:35:12Z")
> [@Prabhu.292](#):
>
> if (doc['question.keyword'].value == 'I need to get parcel as soon as possible','Parcel required as soon as possible') AND Category == 'Expediate'{
> return 'Express';
> }
Your syntax is pretty off - if you have multiple conditions, list them like this:
```auto
if ((doc['question.keyword'].value == 'I need to get parcel as soon as possible' || doc['question.keyword'].value == 'Parcel required as soon as possible') && doc['category.keyword'].value == 'Expediate') { //...
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [February 16, 2021, 9:35am UTC](https://discuss.elastic.co/t/aggregate-the-data-such-that-theres-a-new-column-which-depends-on-the-original-question/260859/10 "2021-02-16T09:35:41Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.