Passing multiselect in Where clause

[jonu4u]

I've a multiselect.

  <label>Grade</label>
  <default>9,6,7</default>
  <fieldForLabel>grade_name</fieldForLabel>
  <fieldForValue>grade_name</fieldForValue>
  <search>
    <query/>
  </search>
  <initialValue>9,6,7</initialValue>
</input>

I want to pass these selected values in a where clause:
]
|inputlookup prod_students.csv | table school_name, school_id,grade_name |eventstats max(grade_name) as mx min(grade_name) as mn by school_name,school_id| where grade_name IN ("$grade_name$")
The above query is not working. I can't move the where next to lookup as I'm doing it on the eventstats field.

[dmarling]

Hello,

The reason why it is failing is because your token is concatenating the results together and wrapping it in quotes. You need to specify the token value prefix and suffix and delimter in your xml like this:

    <input type="multiselect" token="grade_name">
      <label>Grade</label>
      <default>9,6,7</default>
      <fieldForLabel>grade_name</fieldForLabel>
      <fieldForValue>grade_name</fieldForValue>
      <search>
        <query/>
      </search>
      <initialValue>9,6,7</initialValue>
      <valuePrefix>"</valuePrefix>
      <valueSuffix>"</valueSuffix>
      <delimiter>, </delimiter>
    </input>

You also need to remove the quotes around the token in your search so it looks like this:

| inputlookup prod_students.csv 
| table school_name, school_id,grade_name 
| eventstats max(grade_name) as mx min(grade_name) as mn by school_name,school_id 
| where grade_name IN ($grade_name$)

If you do that your output will look like this in the search with the default:

| inputlookup prod_students.csv 
| table school_name, school_id,grade_name 
| eventstats max(grade_name) as mx min(grade_name) as mn by school_name,school_id 
| where grade_name IN ("9","6","7")

Previously it looked like this:

| inputlookup prod_students.csv 
| table school_name, school_id,grade_name 
| eventstats max(grade_name) as mx min(grade_name) as mn by school_name,school_id 
| where grade_name IN ("967")

[manjunathmeti · Accepted solution]

Add delimiter in the input and remove double quotes around the token in search query.

...
<fieldForLabel>grade_name</fieldForLabel>
<fieldForValue>grade_name</fieldForValue>
<delimiter>,</delimiter>

Search query:

|inputlookup prod_students.csv | table school_name, school_id,grade_name |eventstats max(grade_name) as mx min(grade_name) as mn by school_name,school_id| where grade_name IN ($grade_name$) 

And if grade_name is part of prod_students.csv, then query will be(which is much faster):

| inputlookup prod_students.csv where grade_name IN ($grade_name$) | table school_name, school_id,grade_name |eventstats max(grade_name) as mx min(grade_name) as mn by school_name,school_id