Setting field to a variable in load script

[BPetosa]

Hello Everyone,

My goal is to grab people within a certain distance from a specified city. Right now, I set the coordinates for the city in the load script, and call it later in the haversine formula (which works). I would prefer to allow the front end user to choose the city dynamically so I don't have to reload the app every time they want to choose a new city. Here is my current script, along with an example city (set variable). The "Zip" maps to a user DB, and brings along all of their information.

//Miami
LET Lat2 = 25.7617;
LET Long2 = -80.1918;

//==========================================
//Load USCities3 from Attached files 
//==========================================

LOAD
City2,
Zipcode2 as Zip,
Lat3,
Long3,
GeoMakePoint(Lat3,Long3) as Location,

num((((2 *
(atan2(
sqrt((sqr(sin((($(Lat2) * Pi() / 180) - (Lat3 * Pi() / 180)) / 2))
+ ((cos((Lat3 * Pi() / 180)) * cos(($(Lat2) * Pi() / 180)))
* sqr(sin((($(Long2) * Pi() / 180) - (Long3 * Pi() / 180)) / 2))
)
)),
sqrt((1-(
sqr(sin((($(Lat2) * Pi() / 180) - (Lat3 * Pi() / 180)) / 2))
+ ((cos((Lat3 * Pi() / 180)) * cos(($(Lat2) * Pi() / 180)))
* sqr(sin((($(Long2) * Pi() / 180) - (Long3 * Pi() / 180)) / 2))
)
))
)))) * 6371) * 0.621371),'#,##0') as Miles

FROM [lib://AttachedFiles/USCities3.xlsx]
(ooxml, embedded labels, table is Sheet1)

I tried bringing in a duplicate table (USCities3) and renaming the fields so they won't associate with the other table (and left out the haversine). I concatenated them, and did a resident load since lat2/long2 wouldn't be found otherwise. The "city" fields still query each other, so I get no output.

I appreciate any help with this.

Thanks,

Ben

[felipedl · Accepted solution]

Hi Ben,

You could make the user select both cities, by doind two separate data sets of the spreadsheet you have and make them qualified, so they are separate from one another.

After that, you can get the selection of both fields to fill in your expression dinamycally.

So let's say I have this as city "points" for lat and longs:

Qualify *;
Selection_1:
load * Inline
[
City,_Lat,_Long
ABC,1,2
ABD,10,5
ACD,200,100,
X,25.7617,-80.1918
];

Selection_2:
load * Inline
[
City,_Lat,_Long
ABC,1,2
ABD,10,5
ACD,200,100,
X,25.7617,-80.1918

];

I'll have two data sets for every city, so I can select two and make the calculation.

Create 4  variables for each long / lat, like so:

Lat1

=only([Selection_1._Lat])

Lat2

=only([Selection_2._Lat])

Long1

=only([Selection_1._Long])

Long2

=only([Selection_2._Long])

With that, you'll be able to select on the app which cities you want.

Create another variable, with the function you have:

Function

num
(
(
(
(2 *
(atan2(
sqrt((sqr(sin((($(Lat1) * Pi() / 180) - ($(Lat2) * Pi() / 180)) / 2))
+
(
(cos(($(Lat2) * Pi() / 180)) * cos(($(Lat1) * Pi() / 180)))
* sqr(sin((($(Long1) * Pi() / 180) - ($(Long2) * Pi() / 180)) / 2))
)
)),
sqrt((1-(
sqr(sin((($(Lat1) * Pi() / 180) - ($(Lat2) * Pi() / 180)) / 2))
+ ((cos(($(Lat2) * Pi() / 180)) * cos(($(Lat1) * Pi() / 180)))
* sqr(sin((($(Long1) * Pi() / 180) - ($(Long2) * Pi() / 180)) / 2))
)
))
)))) * 6371) * 0.621371),'#,##0') as Miles

And apply as expression the $(Function) (that will expand the expression and the variables within.

Hope it helps,

Felipe.

[BPetosa]

Hi Felipe,

Sorry for the delay, I have been out of town. This got me really close to where I want to be. I think I can figure it out for here, but may comment again. This is really good stuff, and I have some other use cases for it.

Thanks again,

Ben