Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BPetosa
Contributor II
Contributor II

Setting field to a variable in load script

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

 

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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.

View solution in original post

2 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
Contributor II
Contributor II
Author

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