Canvas: filtering on mapped columns

[rozling]

I'm trying to use Canvas to map values so that different data sources can be filtered on a common, mapped column.

I would have expected that a DropDown Filter with a mapped column would filter the same values on the rest of my elements, but that doesn't seem to be working.

See the attached worksheet for an example of what I mean using the demo data; is it possible to filter on mapped columns like this, or am I expecting too much?

Example

A table using demo data:

filters
| demodata
| mapColumn "cool-project"
  fn=${getCell "project" | switch 
{case if={compare eq to="kibana"} then="KIB"}
{case if={compare eq to="logstash"} then="LOG"}
{case if={compare eq to="opbeat"} then="OPB"}
{case if={compare eq to="elasticsearch"} then="ELS"}
    default="other"
  }
| table
| render

A filter which I would expect to filter that table:

demodata
| mapColumn "cool-project"
  fn=${getCell "project" | switch 
{case if={compare eq to="kibana"} then="KIB"}
{case if={compare eq to="logstash"} then="LOG"}
{case if={compare eq to="opbeat"} then="OPB"}
{case if={compare eq to="elasticsearch"} then="ELS"}
    default="other"
  }
| dropdownControl valueColumn="cool-project" filterColumn="cool-project"
| render

Full worksheet of example

{
  "name": "mapColumn Test",
  "id": "workpad-6c44f576-2ce6-49ce-9c0d-59d12fc39b4f",
  "width": 1080,
  "height": 720,
  "page": 0,
  "pages": [
    {
      "id": "page-e45857cf-2426-4cef-b13e-4387a1728c8c",
      "style": {
        "background": "#fff"
      },
      "transition": {},
      "elements": [
        {
          "id": "element-842b2f96-0aaa-4e51-a96f-6a7ff2394ac6",
          "position": {
            "left": 20,
            "top": 20,
            "width": 500,
            "height": 50,
            "angle": 0
          },
          "expression": "demodata\n| mapColumn \"cool-project\"\n  fn=${getCell \"project\" | switch \n{case if={compare eq to=\"kibana\"} then=\"KIB\"}\n{case if={compare eq to=\"logstash\"} then=\"LOG\"}\n{case if={compare eq to=\"opbeat\"} then=\"OPB\"}\n{case if={compare eq to=\"elasticsearch\"} then=\"ELS\"}\n    default=\"other\"\n  }\n| dropdownControl valueColumn=\"cool-project\" filterColumn=\"cool-project\"\n| render",
          "filter": ""
        },
        {
          "id": "element-c7bba917-d8c1-40fe-8f10-37ce0a6608e3",
          "position": {
            "left": 63,
            "top": 204,
            "width": 971,
            "height": 368,
            "angle": 0
          },
          "expression": "filters\n| demodata\n| mapColumn \"cool-project\"\n  fn=${getCell \"project\" | switch \n{case if={compare eq to=\"kibana\"} then=\"KIB\"}\n{case if={compare eq to=\"logstash\"} then=\"LOG\"}\n{case if={compare eq to=\"opbeat\"} then=\"OPB\"}\n{case if={compare eq to=\"elasticsearch\"} then=\"ELS\"}\n    default=\"other\"\n  }\n| table\n| render"
        }
      ]
    }
  ],
  "colors": [
    "#37988d",
    "#c19628",
    "#b83c6f",
    "#3f9939",
    "#1785b0",
    "#ca5f35",
    "#45bdb0",
    "#f2bc33",
    "#e74b8b",
    "#4fbf48",
    "#1ea6dc",
    "#fd7643",
    "#72cec3",
    "#f5cc5d",
    "#ec77a8",
    "#7acf74",
    "#4cbce4",
    "#fd986f",
    "#a1ded7",
    "#f8dd91",
    "#f2a4c5",
    "#a6dfa2",
    "#86d2ed",
    "#fdba9f",
    "#000000",
    "#444444",
    "#777777",
    "#BBBBBB",
    "#FFFFFF",
    "rgba(255,255,255,0)"
  ],
  "@timestamp": "2019-02-25T12:09:40.840Z",
  "@created": "2019-02-25T12:05:16.417Z",
  "assets": {}
}

[Joe_Fleming]

So conceptually, you're close, I think there's just a slight misunderstanding of how filters work here. Every function you use in the expression is "functional", meaning that if you provide it with the same input (context and arguments in Canvas terms), you will always get the same output. They also don't maintain any state, and no value is "global" in the expression.

The disconnect here is how filters works I think. That function produces a filters "context" that is passed into the next function, demodata in your case. For your dropdown filter, you've set valueColumn="cool-project" and filterColumn="cool-project", meaning the dropdown will be populated with the values in the cool-project column. You also set filterColumn="cool-project", so when the filter is applied, it will be "bound" to the cool-project column.

The reason this doesn't work is because demodata doesn't contain a cool-project column, so applying the filter will just remove all the rows, since none of them match. You need to somehow map that filter value back to the original data (ie. the contents of demodata).

The easiest option is to use the original values from demodata in the dropdown.

demodata
| dropdownControl valueColumn="project" filterColumn="project"
| render

That filter will work, you'll still see the cool-project values in the table, and you'll get the right results, but you won't see "KIB" and "LOG" in the dropdown, you'll see "kibana" and "logstash", which isn't really what you want.

You need a way to apply the filter value after you use mapColumn, because cool-project doesn't exist until after that function. That's not something that exists today.

Alternatively, and I'm going to add this shortly now (thanks for the idea), we could enhance the dropdown filter with a new argument displayValue, so then you could have it show your mapped value but actually use the original source value behind the scenes, like this:

| dropdownControl displayColumn=cool-project valueColumn=project filterColumn=project

So you'll see your cool-project value, but when you pick something, it'll actually apply the project value and do what you expect. I created this issue to track the enhancement, and I may have explained it better there: https://github.com/elastic/kibana/issues/32331