# How can I make a join between two file? **URL:** \ **Category:** Elasticsearch\ **Created:** [November 18, 2015, 11:07am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923 "2015-11-18T11:07:58Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![adellarocca](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@adellarocca](https://discuss.elastic.co/u/adellarocca)\ **Post date:** [November 18, 2015, 11:07am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/1 "2015-11-18T11:07:58Z")
Hi! I have two files: us\_data\_flights and us\_states\_geo\_coordinates. a) In the file **us\_data\_flights** there are data about flights in 2015 with information about departure/arrival state, air\_time, delay etc... b) In the file **us states\_geo\_coordinates** there are geo-coordinates for every state. I would like to generate a Map with delay metrics with kibana. How can I make a join between field 'state' of file a) and field 'state" of file b) in order to extract coordinates to plot on map? Thank you very much at all! ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [November 18, 2015, 9:03pm UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/2 "2015-11-18T21:03:53Z")
You cannot, you need to enrich the data during indexing. ---
**Author:** ![adellarocca](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@adellarocca](https://discuss.elastic.co/u/adellarocca)\ **Post date:** [November 19, 2015, 9:14am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/3 "2015-11-19T09:14:45Z")
Thank you for your answer. Could you suggest me how can I join the two file using Logstash? Thank you! ---
**Author:** ![mainec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mainec/32/5557_2.png) [@mainec](https://discuss.elastic.co/u/mainec)\ **Post date:** [November 19, 2015, 10:45am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/4 "2015-11-19T10:45:04Z")
Your problem sounds like you should first denormalize your data before it reaches Elasticsearch. Here are a few pointers to deal with relational data in ES: [https://www.elastic.co/guide/en/elasticsearch/guide/current/relations.html](https://www.elastic.co/guide/en/elasticsearch/guide/current/relations.html) [https://www.elastic.co/guide/en/elasticsearch/guide/current/modeling-your-data.html](https://www.elastic.co/guide/en/elasticsearch/guide/current/modeling-your-data.html) [https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html](https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html) Hope this helps, Isabel ---
**Author:** ![adellarocca](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@adellarocca](https://discuss.elastic.co/u/adellarocca)\ **Post date:** [November 19, 2015, 11:15am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/5 "2015-11-19T11:15:30Z")
Thank you very much for your help! Isabel! ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [November 20, 2015, 5:44am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/6 "2015-11-20T05:44:51Z")
Look at the translate filter. ---
**Author:** ![adellarocca](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@adellarocca](https://discuss.elastic.co/u/adellarocca)\ **Post date:** [November 20, 2015, 10:14am UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/7 "2015-11-20T10:14:16Z")
Thank you very much warkolm! Translate filter is great! But now I have another problem.... 😓 > [@How can I convert to geo\_point a field extracted with translate filter?](https://discuss.elastic.co/t/how-can-i-convert-to-geo-point-a-field-extracted-with-translate-filter/35141): > > Hi! I have extracted geo information with translate filter using a dictionary file YAML as follow location1: 42.1497, -74.9384 location2: 34.5677, -32.8393 ......................................... The logstash conf file translate section is translate{ field =\> "ORIGIN\_STATE\_ABR" destination =\> "ORIGIN\_LOCATION" dictionary\_path =\> "/home/alessandro/Scrivania/dataset/us\_dictionary.yaml" } mutate { convert =\> { "ORIGIN\_LOCATION" =\> "string" } convert =\> { "… 😁 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 11:36pm UTC](https://discuss.elastic.co/t/how-can-i-make-a-join-between-two-file/34923/8 "2017-07-05T23:36:58Z")