Can you help me with a query involving an if condition?

[kingwaras]

Hi,

I can't find the error in my query.

The if condition is not working fine.

In the case that sublevel !="*", the value of the search returns "FALSE" (print screen), but if you run the query in the search panel, it returns a list of emails.

| inputlookup hierarchy_lookup.csv
            |  where [| rest /services/authentication/current-context 
                      | table username
                      | rename username as Name]
            | eval sublevel=split(Sublevel,";") 
            | eval mail=IF(sublevel !="*",sublevel,[search index="effort_tracker"
                                                     | dedup Username
                                                     | table Username ]
                           )
            | mvexpand mail 
            | table mail

Do you have any idea?

Thx

[skoelpin]

Can you explain more of what you're trying to do?

You have a field called sublevel, so I'm assuming you're trying to say, if that field sublevel doesn't return results, then return the value of sublevel? Else, run a search and return a table of usernames?

First, your syntax is wrong on 2 fronts. It should look like this

eval mail=IF(isnull(sublevel),[search index="effort_tracker"
                                                      | dedup Username
                                                      | table Username ], 'sublevel')

While i'm not sure if you can run a search inside an IF statement, the above logic is saying, IF the field sublevel is null, then run that query and return a table of usernames, else if sublevel is not null, then return the value of sublevel

[kingwaras]

Hi @skoelpin , thanks for your reply.
I confirm what said by you. I'm trying to test the value of "Sublevel" field present in the hierarchy_lookup.csv.
If it is null, then run that query and return a table of usernames, else if sublevel is not null, then return the value of sublevel.... but it still does not work well. The first condition works well but not the second (the search).

[skoelpin]

Yep, I don't think you can run a subsearch inside of an eval. You're going to have to add that search before your inputlookup on line 1 then use a simple lookup to append it on your search.

Why are you using inputlookup at all?