topic Re: Is it possible to set a token with the value of the nth row of a result set? in Dashboards & Visualizations

Is it possible to set a token with the value of the nth row of a result set?

andrewtrobec — Fri, 29 Nov 2019 14:00:51 GMT

Hello All,

Using this run anywhere for reference:

| makeresults
| eval fruit = "Apple", amount=54
| append [ | makeresults
 | eval fruit = "Pear", amount=2 ]
| append [ | makeresults
 | eval fruit = "Cantaloupe", amount=5000 ]
| append [ | makeresults
 | eval fruit = "Strawberry", amount=1 ]
 | sort -amount
 | table fruit amount

My objective is to set three tokens with the names of the three fruits with the highest counts (in this case Canteloupe, Apple, Pear) three separate values after the above search completes in a dashboard. I am using the <done> tag to invoke the procedure, but I am having difficulty figuring out how to get it to work. If I set drilldown as follows I can click the value I want:

<drilldown>
   <set token="tok_first">$row.fruit$</set>
</drilldown>

What I'd like to do is take it one step further and have the first three rows get associated to tokens automatically. Something along the lines of:

<done>
   <set token="tok_first">$row[0].fruit$</set>
   <set token="tok_second">$row[1].fruit$</set>
   <set token="tok_third">$row[2].fruit$</set>
</done>

Is this possible?

Thanks!

Andrew

Re: Is it possible to set a token with the value of the nth row of a result set?

jpolvino — Fri, 29 Nov 2019 14:21:33 GMT

One option would be to use head and transpose:

| makeresults
| eval fruit = "Apple", amount=54
| append [ | makeresults
| eval fruit = "Pear", amount=2 ]
| append [ | makeresults
| eval fruit = "Cantaloupe", amount=5000 ]
| append [ | makeresults
| eval fruit = "Strawberry", amount=1 ]
| sort -amount
| table fruit amount
| head 3 | transpose | head 1

This will give you fields row1, row2, and row3 which can then be tokenized.

Re: Is it possible to set a token with the value of the nth row of a result set?

andrewtrobec — Fri, 29 Nov 2019 14:47:37 GMT

@jpolvino Clever! This has solved my problem, thank you!

Re: Is it possible to set a token with the value of the nth row of a result set?

to4kawa — Fri, 29 Nov 2019 14:49:29 GMT

| makeresults
| eval _raw="fruit,amount
Apple,54
Pear,2
Cantaloupe,5000
Strawberry,1"
| multikv forceheader=1 
| sort -amount
| table fruit amount
| head 3
| transpose
| rename "row *" as row*
| head 1

 <done>
    <set token="tok_first">$result.row1$</set>
    <set token="tok_second">$result.row2$</set>
    <set token="tok_third">$result.row3$</set>
 </done>

Is this it?