Displaying a string from the top row first column, possible?

Elastic Stack Kibana
1

Imagine a table like this.

image
image251×101 1.56 KB

I'm using the following query:

| selectFilter
| essql query="
    SELECT
    case
        when delivery_string like '%ups%' then 'UPS'
        when delivery_string like '%dhl%' then 'DHL'
        when delivery_string like '%schenker%' then 'SCHENKER'
        when delivery_string like '%early%' then 'EARLY BIRD'
        when delivery_string like '%best%' then 'BEST TRANSPORT'
        else 'UPS'
    end as freight_companies,
    case
        when delivery_string like '%ups%' then 1
        when delivery_string like '%dhl%' then 2
        when delivery_string like '%schenker%' then 3
        when delivery_string like '%early%' then 4
        when delivery_string like '%best%' then 5
        else 1
    end as order,
    sum(qty_ordered) as value
FROM 'outbound-orders'
WHERE 1 = 1
GROUP BY order
ORDER BY order DESC

What I want to do now is display the top result (i.e., the first freight company in the result set) in a separate box — using the first value in the freight_companies column.

If the table looks like this:

image
image251×101 1.56 KB

I want to display 'UPS' in the box.

If it looks like:
image
I want to display 'DHL'.

Is that possible?

I've tried adding LIMIT 1 and then:

| getCell column="freight_companies"
| render

But it didn't work.

/Jens

2

If you want to render the first row of a query you can use the Markdown panel like this from the kibana_sample_data_logs sample dataset.

kibana
| selectFilter
| essql 
  query="
      SELECT machine.os as os, count(1) as count  
      FROM \"kibana_sample_data_logs\" 
      GROUP BY \"machine.os\" 
      ORDER BY count DESC
      LIMIT 1"
| markdown "{{#with rows.[0]}} Top OS is **{{os}}** with `{{count}}` rows{{/with}}"
| render

image
image1290×844 43.6 KB

Or this markdown if you prefer a table

{{#with rows.[0]}}
| OS | count |
| :--: | :--: |
| {{os}} | {{count}} |
{{/with}}

image
image1334×623 36.5 KB

3

Thank you! :smiley: It worked. Now I got to learn that Markdown language!

4

Hi!
This is the result.
image

Is it possible to change the color of the grey background tile? I would like to have a different color depending of the result.

Something like:

If order = 1 then bgcolor = '#e4f6d0'