Kibana Canvas: createTable creates duplicate rows

Elastic Stack Kibana
1

Hi, I want to create a bar chart in canvas with a consistent 10 bars even if there is no data for 10 bars. It is for a competition and, for example, at the beginning of the week there might not be enough data to have 10 entries yet. Because of the design I do want to display a bar chart with potentially 10 bars so all other values should be zero.

My query does a 'top 10' and might return 6 for example.

I was trying to solve this with createTable thinking that if I create a table with 10 rows and then pipe the results from the query into it it would create some rows with data and some empty

createTable
Creates a datatable with a list of columns, and 1 or more empty rows. To populate the rows, use [ mapColumn ]or [ mathColumn ]

My code (based on examples in the canvas function reference)

filters group='this week'
| var_set 
  name=reps value={essql query="select \"sales.representative.name\" as salesRep from \"index*\" group by salesRep"}
  name=revenue value={essql query="select sum(some-field) as revenue from \"index*\" "}
| createTable rowCount=10
| mapColumn name="salesRep" expression={var "reps" | getCell "salesRep"}
| mapColumn name="revenue" expression={var "revenue" | getCell "revenue"}
| render

It results in a datatable with simply 10 times the same entry, like this:

salesrep revenue
John 1000
John 1000
John 1000
John 1000
John 1000
John 1000
John 1000
John 1000
John 1000
John 1000

So my question is: how should this createTable be used and how should it work?

thanks

2

Hi,

I don't know why you have 2 queries, and the revenue is not the sum of some-field by salesRep (in the same query), but assuming that you want to "complement" a table up to a fixed amount of rows with default values, then I can think of two ways.

The first way would be to have placeholders in your data, i.e. enough fake salesReps in a separate index matching your pattern (e.g. index_placeholders), and by grouping/ordering, you would ensure that the real ones be on the top. You would use the function "| head 10" to take the first 10 rows.

This first solution may not be the cleanest, as your placeholders may appear elsewhere as a side effect etc.

Another solution using only a Canvas expression follows. There are quite a few var and var_set, but it works :slight_smile:

In this example, I've replaced your ESSQL query with a CSV as input :

var_set name='table' value={csv delimiter=',' data='A,B
Tom,1000
John,500
Adam,200'}
| var_set name='rowCountTable' value={var name='table' | rowCount}
| createTable rowCount=10
| var_set name='current' value=-1
| mapColumn name="resultA" expression={var name='current' | var_set name='current' value={math expression='add(value, 1)'} | if condition={var name='current' | lt {var name='rowCountTable'}} then={var name='table' | getCell column="A" row={var name='current'}} else={string 'n/a-' {var name='current'}}}
| var_set name='current' value=-1
| mapColumn name="resultB" expression={var name='current' | var_set name='current' value={math expression='add(value, 1)'} | if condition={var name='current' | lt {var name='rowCountTable'}} then={var name='table' | getCell column="B" row={var name='current'}} else=0}
| alterColumn "resultB" type="number"
| pointseries x='resultA' y='resultB'
| plot defaultStyle={seriesStyle bars=0.75 horizontalBars=false} xaxis=true yaxis={axisConfig min=0}
| render

mapColumn is used once per resulting column: the correct value is fetched from the original table by specifying the row number (otherwise the row is 0, that's why you had always John 1000), up until there is no more data, and the default values are used afterwards.

Let me know how it works for you.

3

Worked like a charm! Thanks a lot!
(the double query was a leftover from some experiment)

4

Hi @charlesfr.rey - I just upgraded to 8.1.3 and the above solution broke unfortunately
I think the part where the 'current' variable is increased by 1 every time to create a table of 10 rows is not working anymore in 8.x, I get a table with 10 the same entries

Any suggestions?
(I tried to fix it but apparently my knowledge is too limited to solve this)

thanks

5

Sorry to hear that ! Unfortunately I'm still on 7.17, can't test it on 8.x right now.

Here is a reformulation, where the row_number is computed once for each table, and then used in the mapColumn expression, hopefully this will fix the issue :

csv delimiter=',' data='A,B
Tom,1000
John,500
Adam,200'
| var_set name='current' value=0
| mapColumn name="row_number" expression={var name='current' | var_set name='current' value={math expression='add(value, 1)'}}
| var_set name='source_table' value={context}
| var_set name='rowCountSourceTable' value={var name='source_table' | rowCount}
| clear
| createTable rowCount=10
| var_set name='current' value=0
| mapColumn name="row_number" expression={var name='current' | var_set name='current' value={math expression='add(value, 1)'}}
| mapColumn name="resultA" expression={getCell column="row_number" | if condition={lt {var name='rowCountSourceTable'}} then={var_set name="current_row" value={context} | var name='source_table' | getCell column="A" row={var name="current_row"}} else={string 'n/a-' {context}}}
| mapColumn name="resultB" expression={getCell column="row_number" | if condition={lt {var name='rowCountSourceTable'}} then={var_set name="current_row" value={context} | var name='source_table' | getCell column="B" row={var name="current_row"}} else=0}
| alterColumn "resultB" type="number"
| pointseries x='resultA' y='resultB'
| plot defaultStyle={seriesStyle bars=0.75 horizontalBars=false} xaxis=true yaxis={axisConfig min=0}
| render
6

Thanks, unfortunately it does not work either

I think this part is broken in 8.x

mapColumn name="row_number" expression={var name='current' | var_set name='current' value={math expression='add(value, 1)'}}

I'll reach out to Elastic support to see what they know

7

Too bad !

Yes, it would be interesting to have an opinion from Elastic on this one, since it worked in previous versions.

Anyway, there is still one more way to do it :

  • the first | mapColumn name="row_number" is not necessary
  • the second one can be initialized manually, i.e. the row_number can come from a csv

Complete example :

csv delimiter=',' data='A,B
Tom,1000
John,500
Adam,200'
| var_set name='source_table' value={context}
| var_set name='rowCountSourceTable' value={var name='source_table' | rowCount}
| clear
| csv delimiter=',' data='row_number
0
1
2
3
4
5
6
7
8
9'
| alterColumn column="row_number" type="number" 
| mapColumn name="resultA" expression={getCell column="row_number" | if condition={lt {var name='rowCountSourceTable'}} then={var_set name="current_row" value={context} | var name='source_table' | getCell column="A" row={var name="current_row"}} else={string 'n/a-' {context | math expression='add(value, 1)'}}}
| mapColumn name="resultB" expression={getCell column="row_number" | if condition={lt {var name='rowCountSourceTable'}} then={var_set name="current_row" value={context} | var name='source_table' | getCell column="B" row={var name="current_row"}} else=0}
| alterColumn "resultB" type="number"
| pointseries x='resultA' y='resultB'
| plot defaultStyle={seriesStyle bars=0.75 horizontalBars=false} xaxis=true yaxis={axisConfig min=0}
| render

image

8

You are one creative person @charlesfr.rey :smiley:
thanks!

9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.