# Is it possible **URL:** \ **Category:** Elasticsearch\ **Created:** [May 27, 2020, 4:28am UTC](https://discuss.elastic.co/t/is-it-possible/234455 "2020-05-27T04:28:24Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [May 27, 2020, 4:28am UTC](https://discuss.elastic.co/t/is-it-possible/234455/1 "2020-05-27T04:28:24Z")
Hi, I have just started on transforms and while doing this I am wondering if something like this (It is what my transform is looking like now): ![Transform1](https://us1.discourse-cdn.com/elastic/original/3X/b/4/b46143294fb0df5ecad640c8ebd305a52f56a8f7.jpeg) Can be converted via Painless scripts (since I am introducing new duration fields) or someother better transfrom strategy into something like this: ![Transform2](https://us1.discourse-cdn.com/elastic/original/3X/5/9/59dd0d6630b853ae0b776102deaf8a9f0d272707.jpeg) I am happy to supply sample data if needed. **EDIT:** Change the pictures to make it more clear. StepName is the name of operation which happens. AvgReading is the value of the parameter of interest at the end of the step. Duration is the number of seconds it takes for the given step to finish. There is no need to calculate. We will use the values as they are. Now that I think of it, it looks like I am trying to transpose a table. ---
**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:** [May 27, 2020, 7:03am UTC](https://discuss.elastic.co/t/is-it-possible/234455/2 "2020-05-27T07:03:27Z")
If I understand correctly you `group_by` serial number. WarmUp, Stable, CoolDown look like counters. The duration fields look like calculation between a start and an end event. This can be done with `scripted_metric` aggregation and custom painless code. I can give more concrete help if you can elaborate a bit. ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [May 27, 2020, 7:15am UTC](https://discuss.elastic.co/t/is-it-possible/234455/3 "2020-05-27T07:15:30Z")
Hi @Hendrik_Muhs, I just added more details. In transform I am grouping by SerialNumber and StepName. Aggregations is done by taking the average value of the AvgReading recorded (kind of pointless since there is only value recorded but that is besides the point). ---
**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:** [May 27, 2020, 7:31am UTC](https://discuss.elastic.co/t/is-it-possible/234455/4 "2020-05-27T07:31:49Z")
Thanks! It wasn't clear to me that the duration are already there, I thought you want to calculate them based on events: start and end. If I look at the 2 tables: Does 1 row correspond to 1 document? So you have 6 input documents and want to merge them into 2 documents, because the other table has 2 rows? This would be a join and can be done with transform, however you have to group by serial number only and than join the docs. Is the 1st table already the output of a transform? In this case you might need another transform on the 1st transform output to get your desired end result. ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [May 27, 2020, 7:53am UTC](https://discuss.elastic.co/t/is-it-possible/234455/5 "2020-05-27T07:53:49Z")
Yes there are 6 documents and I want to merge them into 2 documents. Actually the 1st table I am showing is the result of the transform. Do you have any sample/pseudo code to illustrate the same? ---
**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:** [May 27, 2020, 8:43am UTC](https://discuss.elastic.co/t/is-it-possible/234455/6 "2020-05-27T08:43:25Z")
I am thinking of something like this: ```auto "scripted_metric": { "init_script": "state.join = new HashMap()", "map_script": """if (params['_source']['StepName'] == 'WarmUp'){ state.join.put('WarmUpDuration', params['_source']['Duration']) } else if (param... { ... } }""", "combine_script": "return state.join", "reduce_script": "Map j=new HashMap(); for (s in states) {j.putAll(s)} return j;" } ``` In the `map_script` you rename the columns and in the `reduce_script` you join. Because of the rename in map, columns should not conflict. There are 2 more things to know: - transform can not create mappings for scripted metric, to get to the data types you want in the destination index, you need to create the destination index yourself with the according mappings - the output of the scripted metric is nested under the name you use for the aggregation (`name.SerialNumber`, `name.WarmUpDuration`, etc.), to get rid of `name` you can use an ingest pipeline with a [rename processor](https://www.elastic.co/guide/en/elasticsearch/reference/current/rename-processor.html), the pipeline can be specified as part of the transform destination ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [May 27, 2020, 11:44pm UTC](https://discuss.elastic.co/t/is-it-possible/234455/7 "2020-05-27T23:44:24Z")
This is way beyond my scripted metric competency. Give me a week to digest it and come back on this. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 24, 2020, 11:44pm UTC](https://discuss.elastic.co/t/is-it-possible/234455/8 "2020-06-24T23:44:37Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.