Use eval statement with and, if, then

[mistydennis]

Example: I'm trying to count how many books we have in our database based on subject: children's, romance, travel, etc. Right now I have a chart that lists out the subject and the count. But I need to pull out a certain type of book and break it down into further categories based on additional metadata. What statement can I use for something like this:

if book=travel AND author=James, then localtravel. If not, foreigntravel.

I'm trying to get everything to fall into one column beneath "Book Type" so it reads:

Children
Romance
LocalTravel
ForeignTravel

[woodcock · Accepted solution]

Like this:

Your Base Search Here
| eval book = case(
    (book="travel" AND author="James"), "localtravel",
        ...
    Other "localtravel" Cases Here
        ...
    (book="travel"),                    "foreigntravel",
        ...
    Other Cases Here
        ...
    true(),                             book)

[mistydennis]

Thank you, that's exactly what I was looking for!

[clara-fication]

you can do a if statement:

|eval BookType=if(book="travel" AND author="James","LocalTravel","ForeignTravel")

if you need to add more to it, use a case statement.

if you can create a lookup table with all the fields you'd like to compare, that might be best, if there are a lot.