How to group values with similar pattern into the choices in a dropdown?

[Julia1231]

Hi, 

I'm searching a way to create a dropdown in Dashboard, in which the values in the dropdown is grouped.

For example with the table below, I want to have a dropdown with 2 values called "Site": Washington, California. Then later in the statistic table, it will list IDs according to the "Site"

Address	ID
1 Microsoft Way, Redmond, Washington	132
15 Microsoft Way, Redmond, Washington	456
10 Microsoft Way, Redmond, Washington	789
1 Infinite Loop, Cupertino, California	111
2 Infinite Loop, Cupertino, California	222
3 Infinite Loop, Cupertino, California	489

I imagine to have a list of label in the dropdown, then search for the values correspond with each lable:

    <input type="dropdown" token="site" searchWhenChanged="true">
      <label>Site</label>
      <choice value="Washington">Washington</choice>
      <choice value="California">California</choice>
      <choice value="*">All</choice>
    </input>

|inputlookup address.csv
|where like(Address,"%Washington%")

But  I don't know how to put it together to work.

Do you have an idea how it can work or another idea, please?

Thanks in advanced!

[ITWhisperer]

Try something like this

|inputlookup address.csv
|where match(Address,$site$)

[Julia1231 · Accepted solution]

@ITWhisperer Thanks for your reply,  I don't know why but it shows 0 result for me.

However I find this works:

|inputlookup address.csv 
|eval site=case(like(address,"%Washington%"), "Washington", like(address,"%California%"), "California",1=1,"No")