Query simplification with ifelse function

Q&A
,
1

Hello team, I would like to see if an ifelse query can be simplified when multiple variables must be determined.

For example:

ifelse(
    extract("WD",{Start Time}) = 1 
    OR extract("WD",{Start Time}) = 7, "Weekend", "Weekday")

Is there a way to simplify this syntax to something like this?

ifelse(
    extract("WD",{Start Time}) = (1 OR 7), "Weekend", "Weekday")

I use a simple example to explain the idea but the logic of this simplification can be applied to a large number of calculations.

2

being a SQL developer I wanted to use extract(“WD”,{Start Time}) in (1,7) as the logic test (before the first comma in the ifelse). However, having used QS for over two years, I can’t remember an instance where I had used a construct like that. So I went and looked at the documentation at Ifelse - Amazon Quick Sight, and learned something new. There’s a locate function that might be useful in your situation.

ifelse (
locate (‘1,7’, extract(“WD”,{Start Time}) ) > 0, /*locate looks for 1 or 7 in what is extracted, and returns a number, which compared to 0 returns a boolean true or false which is what the ifelse needs */
“Weekend”,
“Weekday”
)

3

Wooooow @cgreenacre, I had never thought of such a combination of functions. I think it’s a very ingenious way to solve this problem and I could extend that same idea to other types of calculations that are not numerical.

I regularly have to do custom fields with ifelse functions, where a single data must be validated through multiple options (like “OR” validations), which is why the syntax sometimes becomes too big; I think this may be the starting point to solve this type of problem.

If I find other useful combinations, I will continue posting in this topic in case it can be useful to someone. Thaaanks!