map 5 unequal ranges to id B
This is actually coming from a business place, but it's all about being able to do math tricks, and that's why I'm posting here.
I want to implement the following function:
f(x)=
1, x ∈ (-∞,0]
2, x ∈ (0,1500]
3, x ∈ (1500,5000]
4, x ∈ (5000,10000]
5, x ∈ (10000,+∞)
The problem is I am using a specific language (QlikView/QlikSense), which does not have any case-like statements. Currently I am using the all-too-common nested-if solution:
if(x>10000,5,(if x>5000,4,.......))
It works. The problem is that x is a really long and really heavy business calculation formula. Repeating it multiple times not only makes the formula a headache, but also makes me paranoid about the language itself executing the same calculation multiple times.
That's why I turned to this impressively creative community. Can you find a way, using common math functions, to create this formula without using x more than once?
The language has most math funcitons implemented. There are a few of its own as well, but I doubt they are going to help (There is already a range-splitting one, but it won't work with non-symmetrical ranges like here). Nothing to stop you from researching though.
-
$\\begingroup$ I'm a little unsure about the restrictions. Are we disallowed from using, say, a polynomial, where $x$ appears more than once? Or could we define a function which maps to a polynomial expression and then use that function. $\\endgroup$ – hexomino 8 hours ago
-
$\\begingroup$ Surely, I'd like to avoid x more than once, else I'd have gone on the polynomial way. Creating an auxiliary function would still use x multiple times, so I guess that's out of spec too. $\\endgroup$ – George Menoutis 8 hours ago
-
$\\begingroup$ is the use of an array of lenght, lets say ... 22 allowed ? $\\endgroup$ – Neil 8 hours ago
-
$\\begingroup$ @hexomino you see hex, trying to find the answer to this question is especially difficult. There is a large amount of hearsay and superstitions about how the tool works and access to the true engineers seems impossible, at least to my moderate experience. I'm talking about the engine implementation - on the user level (which I am) no, there is not. $\\endgroup$ – George Menoutis 7 hours ago
-
$\\begingroup$ Does the language have an iferror function where it calculates something and returns it unless it's an error in which case it returns something else $\\endgroup$ – Dr Xorile 7 hours ago
1 Answer
Not sure about the possibility of a dictionnary, but maybe this can do what you want : divide X by 500 and cap the result between 0 and 21, then use an array
dict = [1,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,5];
dict[min(21,max(0,(x/500)))];
the part with the array is kinda bad because it means a very big array depending on the unequal ranges, but in your specific case it seems acceptable if we cant find a function that changes exactly on 0,3,10 and 20 and use only one x
-
1$\\begingroup$ So simple! Thanks! I might later post the real code just for the sake of it. $\\endgroup$ – George Menoutis 7 hours ago
-
$\\begingroup$ As promised:
pick(1+rangemin(21,rangemax(0,ceil(x/(500-0.00001)))),1,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,5)-1)). Thanks again! $\\endgroup$ – George Menoutis 6 hours ago