Issue with expression: List of Depots by the close...

Daisy_Ramirez · 08-28-2019 01:48 PM

Hello, I may be overthinking this one. I have a Quote form where users can input their destination zip code and then select a Depot to make their purchase which is an Enum field. I’ve been struggling with the right expression for the list to display all depots sorted by the closest distance to the Destination

*(Destination I cross reference the zip code entered with a zip codes table to id the Geopoint - hence the look up.)
*[vir_depot_geopoint] is the geopoint of the Depots

Can someone assist with the expression? Any help would be outstanding - Thanks so much!

ORDERBY(
SELECT(Depot[Depot_ID],
DISTANCE(LOOKUP([Destination],“US Zip Codes”,“Zip”,“Geopoint”),[vir_depot_geopoint])<500),[Depot])

MultiTech

Hmmm… not sure about the ORDERBY part, but have you tried the select by itself? Does it return a list of depots like you want?

Try this for the select instead:

SELECT(Depot[Depot_ID],
DISTANCE(LOOKUP([_thisrow].[Destination],“US Zip Codes”,“Zip”,“Geopoint”), [vir_depot_geopoint]) < 500)
)
I added in [_thisrow] to specify which column should come from the table this formula lives in vs. a column in the table we’re doing a select on (aka, the Depot table).

Daisy_Ramirez

Thanks so much Steve and Multitech. No luck on the select statement. In order to build the list for a user to select from, I was thinking something like this might work as I simply need the Depot and the distance from the [Destination] to appear in the list for them to choose from:

LIST(Depot[Depot_ID],
DISTANCE(LOOKUP([_thisrow].[Destination],“US Zip Codes”,“Zip”,“Geopoint”),LOOKUP(Depot[Zip],“US Zip Codes”,“Zip”,“Geopoint”)))

Got this error: Cannot compare Text with list Depot[Zip]

MultiTech

You’ll need to provide a single value in that space

Bellave_Jayaram

Looks like you need to create a VC that pre-computes the DISTANCE between Destination and EVERY Depot which seems unnecessary as you are interested only in the nearest but I don’t think there is any other way to do what you are looking for unless you use sheet formulas or scripting to build your list for likely Destination ZIP codes.

Daisy_Ramirez

Hi Bellave, I would be fine with the VC that pre-computes the distance between Destination and EVERY depot as I can sort it by the distance. Would the VC reside in the Depot’s table or the Quote table? What would the formula look like for that VC? Thanks Bellave

MultiTech

The VC would need to be in the Depot table, and the formula would look something like this:

DISTANCE(HERE(), [vir_depot_geopoint])

Then your SELECT() formula to gather all the rows would be much simpler:

TOP(ORDERBY(Depot[Depot_ID], [Active_Distance]), 20)

Where [Active_Distance] is the new VC.

Daisy_Ramirez

Thanks so much but it seems DISTANCE (HERE(),[vir_depot_geopoint]) would be capturing the distance of the actual entry point and the depot in the Depot table. I’m needing for the distance to be calculated at the Quote level when the user inputs the zip code they’re in. I can capture the geopoint at the Depot table but will need the Depot listing (accessed by in the quote by dropdown) to display the number of km/miles from the zip code the user entered in the Quote.

So much thanks for the help - I feel like we’re close

Bellave_Jayaram

I think then you would need a third helper table which is a lookup table of distances between the geo point of each zip code and all other zip codes.

Steve

Your expression, reformatted for my clarity and with @MultiTech_Visions’ good suggestion to dereference _THISROW:

ORDERBY(
  SELECT(
    Depot[Depot_ID],
    (
      DISTANCE(
        LOOKUP(
          [_THISROW].[Destination],
          “US Zip Codes”,
          “Zip”,
          “Geopoint”
        ),
        [vir_depot_geopoint]
      )
      < 500
    )
  ),
  [Depot]
)

Your expression uses SELECT() to gather all Depot rows with a distance less than 500 km from the destination ZIP code, then orders those rows by whatever the Depot column of the Depot table contains, in ascending (A-Z) order.

This will get you the 20 closest depots:

TOP(
  ORDERBY(
    Depot[Depot_ID],
    DISTANCE(
      LOOKUP(
        [_THISROW].[Destination],
        “US Zip Codes”,
        “Zip”,
        “Geopoint”
      ),
      [vir_depot_geopoint]
    )
  ),
  20
)

MultiTech

Epic

MultiTech

@Daisy_Ramirez I think the solution is a hybrid of what we’ve got here:

In the Depot table, you need a VC that calculates the distance from the zip entered in the quote; but to find this zip code you need a way to isolate out the quote from all the rest, this way you can use a lookup to find the zip code’s geopoint and calculate a distance from that.

Isolating out the individual quote can be accomplished with a slice, you’ll need to create a condition formula that singles out the quote the user of the app is currently dealing with.

Once you have this slice, you can use a lookup to find the geopoint and then get the distance.

LOOKUP(any(Active_Quote[Destination]),“US Zip Codes”,“Zip”,“Geopoint”)

Insert this into the DISTANCE() formula on the depot table; this field will then hold the distance between the active quote’s destination and the geopoint of the depot.

When you do you’re select() to get the top 20, each depot has a filed that’s calculated the distance between it and the zip geopoint indicated in the active quote - using this you should get your top 20.

Steve

TOP(
  ORDERBY(
    Depot[Depot_ID],
    DISTANCE(
      LOOKUP(
        ANY(Active_Quote[Destination]),
        “US Zip Codes”,
        “Zip”,
        “Geopoint”
      ),
      [vir_depot_geopoint]
    )
  ),
  20
)

Issue with expression: List of Depots by the closest distance