# Kibana Canvas: createTable creates duplicate rows

**URL:** <https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [March 31, 2022, 2:28pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210 "2022-03-31T14:28:30Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![JeroenK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jeroenk/32/103640_2.png) [@JeroenK](https://discuss.elastic.co/u/JeroenK)\
**Post date:** [March 31, 2022, 2:28pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/1 "2022-03-31T14:28:30Z")

</div>

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)

```auto
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

---

<div class="post-metadata">

**Author:** ![charlesfr.rey](https://avatars.discourse-cdn.com/v4/letter/c/c6cbf5/32.png) [@charlesfr.rey](https://discuss.elastic.co/u/charlesfr.rey)\
**Post date:** [April 1, 2022, 8:11pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/2 "2022-04-01T20:11:55Z")

</div>

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 🙂

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![JeroenK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jeroenk/32/103640_2.png) [@JeroenK](https://discuss.elastic.co/u/JeroenK)\
**Post date:** [April 5, 2022, 4:52pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/3 "2022-04-05T16:52:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JeroenK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jeroenk/32/103640_2.png) [@JeroenK](https://discuss.elastic.co/u/JeroenK)\
**Post date:** [April 26, 2022, 9:44am UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/4 "2022-04-26T09:44:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![charlesfr.rey](https://avatars.discourse-cdn.com/v4/letter/c/c6cbf5/32.png) [@charlesfr.rey](https://discuss.elastic.co/u/charlesfr.rey)\
**Post date:** [May 16, 2022, 10:42pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/5 "2022-05-16T22:42:05Z")

</div>

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 :

```auto
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

```

---

<div class="post-metadata">

**Author:** ![JeroenK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jeroenk/32/103640_2.png) [@JeroenK](https://discuss.elastic.co/u/JeroenK)\
**Post date:** [May 23, 2022, 10:15am UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/6 "2022-05-23T10:15:47Z")

</div>

Thanks, unfortunately it does not work either

I think this part is broken in 8.x

```auto
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

---

<div class="post-metadata">

**Author:** ![charlesfr.rey](https://avatars.discourse-cdn.com/v4/letter/c/c6cbf5/32.png) [@charlesfr.rey](https://discuss.elastic.co/u/charlesfr.rey)\
**Post date:** [May 23, 2022, 1:18pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/7 "2022-05-23T13:18:00Z")

</div>

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 :

```auto
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](https://us1.discourse-cdn.com/elastic/original/3X/7/1/7109e2fe9d96c37647f80d3940180268d351f052.png)

---

<div class="post-metadata">

**Author:** ![JeroenK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jeroenk/32/103640_2.png) [@JeroenK](https://discuss.elastic.co/u/JeroenK)\
**Post date:** [May 23, 2022, 3:52pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/8 "2022-05-23T15:52:42Z")

</div>

> [@charlesfr.rey](#):
>
> ```auto
> | 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"
> 
> ```

You are one creative person @charlesfr.rey 😃  
thanks!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 20, 2022, 3:53pm UTC](https://discuss.elastic.co/t/kibana-canvas-createtable-creates-duplicate-rows/301210/9 "2022-06-20T15:53:14Z")

</div>

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