trying to make a column in my Dataview table that takes a number and checks it against a set of ranges, then returns a number depending on what, set of ranges it fits into.
example
0-1 returns “Class A”
2-3 returns “Class B”
4-5 returns “Class C”
6-7 returns “Class D”
8-9 returns “Class E”
This may be more than what Data View is capable of. But if is possible, I’m not sure where to start trying to figure it out.
One possibility could be to use choice(), as in something like the following, with your number in number:
FLATTEN
CHOICE(number <= 1, "Class A",
CHOICE(number <= 3, "Class B",
CHOICE(number <= 5, "Class C",
CHOICE(number <= 7, "Class D",
CHOICE(number <= 9, "Class E", "unknown"))))) as class
Now class should hold the corresponding class, readily available for use as a value in other parts of your query. The way to read this structure is that the choice functions takes a comparison as its first parameter, f.ex. number <= 1, and if that is true, it select the second parameter, "Class A", and if false, it selects the third parameter, which happens to be yet another CHOICE(...) function.
This continues down the chain until it either hits a true value, and returns that class, or comes to check number <= 9, and finds that also false, where it then returns the "Unknown" value.
( A FLATTEN clause typically goes right before the WHERE clause, and after the FROM clause (if it exists) )
That works like a charm.
Thank you very much for the Assistance.