Creating a new table with multiple rows in Canvas

Elastic Stack Kibana
1

Hello,

I am trying to create a new table based on 2 different queries.

But its only giving me the first row:

image
image692×95 2.92 KB

This is my code:

var_set
  name="Relet" value={essql "select Service_Provider, round(avg(Score),2) as a  from (select Answer, Service_Provider, Response_ID, Question_code,  case when Answer=60 or Answer=80 or Answer=100 then 1 else 0 end as Score from \"preprocessed_transformed\" where Question_code IN ('Reletting') and client='yes' and record_type='survey_metric') group by Service_Provider" }
  name="Share" value={essql "select Service_Provider,round(avg(Score),2) as b  from (select Answer, Service_Provider, Response_ID, Question_code,  case when Answer=60 or Answer=80 or Answer=100 then 1 else 0 end as Score from \"preprocessed_transformed\" where Question_code IN ('Share_Of_Wallet') and client='yes' and record_type='survey_metric') group by Service_Provider" }
| createTable ids="Reletting" ids="Share_Of_Wallet" ids="Provider" 
| mapColumn "Reletting"   fn={ var "Relet"  |getCell "a" }
| alterColumn column="Reletting" type="number"
| mapColumn "Share_Of_Wallet"   fn={ var "Share"  |getCell "b" }
| alterColumn column="Share_Of_Wallet" type="number"
| mapColumn "Provider" fn={var  "Share" | getCell "Service_Provider"}
| alterColumn column="Provider" type= "string" 

| render

If I use one of the queries, I see that it has a lot of rows:

image

essql  query="select Service_Provider,round(avg(Score),2) as b  from (select Answer, Service_Provider, Response_ID, Question_code,  case when Answer=60 or Answer=80 or Answer=100 then 1 else 0 end as Score from \"preprocessed_transformed\" where Question_code IN ('Share_Of_Wallet') and client='yes' and record_type='survey_metric') group by Service_Provider"
| table
| render

I even tried it with 1 mapping, but it still giving me 1 row:

image
image707×137 2.75 KB 

var_set
  name="Relet" value={essql "select Service_Provider, round(avg(Score),2) as a  from (select Answer, Service_Provider, Response_ID, Question_code,  case when Answer=60 or Answer=80 or Answer=100 then 1 else 0 end as Score from \"preprocessed_transformed\" where Question_code IN ('Reletting') and client='yes' and record_type='survey_metric') group by Service_Provider" }
| createTable ids="Reletting"
| mapColumn "Reletting"   fn={ var "Relet"  |getCell "a" }
| render

How can I get all the rows while creating a new table with 3 columns?

2

Well I guess the issue is that you are using getCell function. Are you shure that this returns the full table?

Also.. did you try to use the debug output to find the issue?

3

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