Merging fields of two index pattern

Hi,

I have two data streams in Kibana with similar information. I am trying to aggregate them by creating a new index pattern. Each of them has a field "state". For one of them, the state can be [passed, failed], and for the other one is [Succeeded, Failed]. I want to aggerate passed with Succeeded and failed with Failed. This is my new index pattern:

when I try to simply visualize the state for the merged index pattern, I get the below chart, which is expected:

To merge states, I defined a lookup for merged index pattern as below:

that I expected to merge the states. But I get this in visualization:

So my question is, how can I merge the data from both streams?

Sorry for the late reply, have you solved this? Which version of the stack are you running?

You should use runtime fields to do this type of data manipulation at query time. For a simple dataset following your data:

PUT discuss-315564-one
{
  "mappings": {
    "properties": {
      "state": {"type": "keyword"}
    }
  }
}

PUT discuss-315564-one/_bulk
{ "index": {}}
{ "state": "Succeeded" }
{ "index": {}}
{ "state": "Succeeded" }
{ "index": {}}
{ "state": "Failed" }
{ "index": {}}
{ "state": "Succeeded" }

PUT discuss-315564-two
{
  "mappings": {
    "properties": {
      "state": {"type": "keyword"}
    }
  }
}

PUT discuss-315564-two/_bulk
{ "index": {}}
{ "state": "success" }
{ "index": {}}
{ "state": "success" }
{ "index": {}}
{ "state": "fail" }
{ "index": {}}
{ "state": "success" }

# Check 8 docs are returned
GET discuss-315564-*/_search

# Cleanup
DELETE discuss-315564-index-*

A data view with this new runtime field:

String state = doc['state'].value;

if (state ==  'Succeeded' ||  state ==  'success') emit('yes');
else if (state ==  'Failed' || state ==  'fail') emit('no');
else emit ('n/a');

Will render on Lens without issues