Compare a number with variable number ranges

fdmsaraiva
Participant V

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 Solved
0 2 415
1 ACCEPTED SOLUTION

Steve
Participant V

Try:

ISNOTBLANK(
  FILTER(
    "Route_Ranges",
    AND(
      ([_THISROW].[Postcode] <= [Range_Max]),
      ([_THISROW].[Postcode] >= [Range_Min])
    )
  )
)

View solution in original post

2 REPLIES 2

Steve
Participant V

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.

Top Labels in this Space