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

[andrewtrobec]

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

[jpolvino · Accepted solution]

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.

[to4kawa]

| 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?

[andrewtrobec]

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