topic Setting field to a variable in load script in Qlik Sense App Development
https://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1581674#M42495
<P>Hello Everyone,</P><P> </P><P>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.</P><P>//Miami<BR />LET Lat2 = 25.7617;<BR />LET Long2 = -80.1918;</P><P>//==========================================<BR />//Load USCities3 from Attached files <BR />//==========================================</P><P>LOAD<BR />City2,<BR />Zipcode2 as Zip,<BR />Lat3,<BR />Long3,<BR />GeoMakePoint(Lat3,Long3) as Location,<BR /><BR />num((((2 *<BR />(atan2(<BR />sqrt((sqr(sin((($(Lat2) * Pi() / 180) - (Lat3 * Pi() / 180)) / 2))<BR />+ ((cos((Lat3 * Pi() / 180)) * cos(($(Lat2) * Pi() / 180)))<BR />* sqr(sin((($(Long2) * Pi() / 180) - (Long3 * Pi() / 180)) / 2))<BR />)<BR />)),<BR />sqrt((1-(<BR />sqr(sin((($(Lat2) * Pi() / 180) - (Lat3 * Pi() / 180)) / 2))<BR />+ ((cos((Lat3 * Pi() / 180)) * cos(($(Lat2) * Pi() / 180)))<BR />* sqr(sin((($(Long2) * Pi() / 180) - (Long3 * Pi() / 180)) / 2))<BR />)<BR />))<BR />)))) * 6371) * 0.621371),'#,##0') as Miles</P><P><BR />FROM [lib://AttachedFiles/USCities3.xlsx]<BR />(ooxml, embedded labels, table is Sheet1)</P><P> </P><P>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.</P><P> </P><P>I appreciate any help with this.</P><P>Thanks,</P><P>Ben</P><P> </P>Fri, 17 May 2019 17:15:19 GMTBPetosa2019-05-17T17:15:19ZSetting field to a variable in load script
https://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1581674#M42495
<P>Hello Everyone,</P><P> </P><P>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.</P><P>//Miami<BR />LET Lat2 = 25.7617;<BR />LET Long2 = -80.1918;</P><P>//==========================================<BR />//Load USCities3 from Attached files <BR />//==========================================</P><P>LOAD<BR />City2,<BR />Zipcode2 as Zip,<BR />Lat3,<BR />Long3,<BR />GeoMakePoint(Lat3,Long3) as Location,<BR /><BR />num((((2 *<BR />(atan2(<BR />sqrt((sqr(sin((($(Lat2) * Pi() / 180) - (Lat3 * Pi() / 180)) / 2))<BR />+ ((cos((Lat3 * Pi() / 180)) * cos(($(Lat2) * Pi() / 180)))<BR />* sqr(sin((($(Long2) * Pi() / 180) - (Long3 * Pi() / 180)) / 2))<BR />)<BR />)),<BR />sqrt((1-(<BR />sqr(sin((($(Lat2) * Pi() / 180) - (Lat3 * Pi() / 180)) / 2))<BR />+ ((cos((Lat3 * Pi() / 180)) * cos(($(Lat2) * Pi() / 180)))<BR />* sqr(sin((($(Long2) * Pi() / 180) - (Long3 * Pi() / 180)) / 2))<BR />)<BR />))<BR />)))) * 6371) * 0.621371),'#,##0') as Miles</P><P><BR />FROM [lib://AttachedFiles/USCities3.xlsx]<BR />(ooxml, embedded labels, table is Sheet1)</P><P> </P><P>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.</P><P> </P><P>I appreciate any help with this.</P><P>Thanks,</P><P>Ben</P><P> </P>Fri, 17 May 2019 17:15:19 GMThttps://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1581674#M42495BPetosa2019-05-17T17:15:19ZRe: Setting field to a variable in load script
https://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1581689#M42498
<P>Hi Ben,</P><P>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.</P><P>After that, you can get the selection of both fields to fill in your expression dinamycally.</P><P> </P><P>So let's say I have this as city "points" for lat and longs:</P><P> </P><P>Qualify *;<BR />Selection_1:<BR />load * Inline<BR />[<BR />City,_Lat,_Long<BR />ABC,1,2<BR />ABD,10,5<BR />ACD,200,100,<BR />X,25.7617,-80.1918<BR />];</P><P>Selection_2:<BR />load * Inline<BR />[<BR />City,_Lat,_Long<BR />ABC,1,2<BR />ABD,10,5<BR />ACD,200,100,<BR />X,25.7617,-80.1918</P><P>];</P><P> </P><P>I'll have two data sets for every city, so I can select two and make the calculation.</P><P> </P><P>Create 4 variables for each long / lat, like so:</P><P>Lat1</P><P>=only([Selection_1._Lat])</P><P>Lat2</P><P>=only([Selection_2._Lat])</P><P>Long1</P><P>=only([Selection_1._Long])</P><P>Long2</P><P>=only([Selection_2._Long])</P><P> </P><P>With that, you'll be able to select on the app which cities you want.</P><P>Create another variable, with the function you have:</P><P>Function</P><P>num<BR />(<BR />(<BR />(<BR />(2 *<BR />(atan2(<BR />sqrt((sqr(sin((($(Lat1) * Pi() / 180) - ($(Lat2) * Pi() / 180)) / 2))<BR />+<BR />(<BR />(cos(($(Lat2) * Pi() / 180)) * cos(($(Lat1) * Pi() / 180)))<BR />* sqr(sin((($(Long1) * Pi() / 180) - ($(Long2) * Pi() / 180)) / 2))<BR />)<BR />)),<BR />sqrt((1-(<BR />sqr(sin((($(Lat1) * Pi() / 180) - ($(Lat2) * Pi() / 180)) / 2))<BR />+ ((cos(($(Lat2) * Pi() / 180)) * cos(($(Lat1) * Pi() / 180)))<BR />* sqr(sin((($(Long1) * Pi() / 180) - ($(Long2) * Pi() / 180)) / 2))<BR />)<BR />))<BR />)))) * 6371) * 0.621371),'#,##0') as Miles</P><P> </P><P>And apply as expression the $(Function) (that will expand the expression and the variables within.</P><P> </P><P>Hope it helps,</P><P> </P><P>Felipe.</P>Fri, 17 May 2019 18:20:57 GMThttps://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1581689#M42498felipedl2019-05-17T18:20:57ZRe: Setting field to a variable in load script
https://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1582987#M42616
<P>Hi Felipe,</P><P>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.</P><P>Thanks again,</P><P>Ben</P>Tue, 21 May 2019 20:42:30 GMThttps://community.qlik.com/t5/Qlik-Sense-App-Development/Setting-field-to-a-variable-in-load-script/m-p/1582987#M42616BPetosa2019-05-21T20:42:30Z