I need help formulating a query that would allow me to get a Y/N output by matching a number against a list of number ranges.
Say you have a table of ranges with [Range_min] and [Range_max] columns, where each row defines a different numeric interval, and you need to determine if a given number is contained in any of the intervals defined by those rows.
The use case is numeric postcodes and deliveries. I would like to be able to have a Route entity, where I populate related Route_Ranges, and where I have actions to see all Deliveries matching the related ranges. For the action I reckon Iโd use a LINKTOFILTEREDVIEW(โDeliveriesโ, FILTER) expression, but I donโt see how to define a filter that will act as a kind of for loop, parse every related Route_Range, see if the postcode is greater than its Range_min and smaller than its Range_max, and return true if any of the related Route_Ranges returns true.
Thoughts?
Solved! Go to Solution.
Try:
ISNOTBLANK(
FILTER(
"Route_Ranges",
AND(
([_THISROW].[Postcode] <= [Range_Max]),
([_THISROW].[Postcode] >= [Range_Min])
)
)
)
Try:
ISNOTBLANK(
FILTER(
"Route_Ranges",
AND(
([_THISROW].[Postcode] <= [Range_Max]),
([_THISROW].[Postcode] >= [Range_Min])
)
)
)
The business requirement went away so I never got round to trying this, but thank you very much @Steve, Iโll be sure to come back here if a similiar requirement comes up.
User | Count |
---|---|
60 | |
25 | |
13 | |
12 | |
6 |