topic Re: Return URL generated in VBscript to Macro in QlikView

Return URL generated in VBscript to Macro

Wed, 14 Nov 2012 09:01:23 GMT

Hi Folks,

I recently posted about an issue I'm having with URL signing of Static Maps in GoogleMaps for Business, which is required in order to record usage statistics. A number of problems have derailed this but I think I've now got a solution, which I shall post up if it pans out.

The issue around signing is that the facility is not available within Jscript or VBScript without downloading an ActiveX component which is not a viable solution. Further, each unique call needs to be individually signed, thereby requiring a dynamic solution. As such, I've proceeded by creating and deploying a WebService that takes the address, zoom level, channel, and the client id and private key, carries out the signing and finally returns the signed URL. I've then written a VBscript routine that consumes this web service and stores the signed URL in a variable.

So what I need to do now is to somehow call this VBscript function in the script below:

let noRows = NoOfRows('Table1')-1;

for i=0 to $(noRows)

let a=peek('FirstName',$(i),'Table1');
let b=peek('LastName',$(i),'Table1');
let c=peek(‘ProductSold’,$(i),'Table1');
let address=peek('City',$(i),'Table1');

Data:
LOAD
'$(a)' as FirstName,
'$(b)' as LastName,
'$(c)' as ProductSold,
'$(address)' as Address,
subfield([Response/Placemark/Point/coordinates], ',' ,1) AS longitude,
subfield([Response/Placemark/Point/coordinates], ',' ,2) AS latitude
FROM [http://maps.google.com/maps/geo?q=$(address)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [kml]);

next

replacing FROM [http://maps... etc with my routine (obviously passing the parameters too).

Can anyone shed any light on how I might acheive this?

Thanks,

Jes

Re: Return URL generated in VBscript to Macro

Thu, 15 Nov 2012 11:44:36 GMT

Hi Folks,

Ok, a bit of a mistake in the above. What I actually need to do is, in the "What's new in Qlikview11.qvw" example under the the customers tab the chart Dynamic Image is recorded as:-

In the Colour tab, do the changes as mentioned in the screenshot below.

For Dynamic Image, paste the URL

='http://maps.google.com/staticmap?center='
&
num(var_mid_lat, '##############', '.', ',' )
&
','
&
num(var_mid_long, '##############', '.', ',' )
&
'&zoom=$(var_zoom)'
&
'&size='&map_size_x&'x'&map_size_y
&
'&key='&gmap_key
&
'&maptype='&var_maptype
& '.jpg'

How can I can this so that instead it uses the results of a VBScript function GetMyMap(lat,lng)?

Where should I define this function?

Thanks, in advance,

Jes

Re: Return URL generated in VBscript to Macro

Gysbert_Wassenaar — Thu, 15 Nov 2012 12:36:30 GMT

Try setting the url for the dynamic image to a variable and have a macro set the variable. You call the macro with actions on the OnChange trigger of the variables lat and lng.

Re: Return URL generated in VBscript to Macro

Thu, 15 Nov 2012 12:49:38 GMT

Hi Gysbert,

I'm very much of a newbie to Qlikview; any chance you could post an example or a link to something..?

Thanks,

Jes

Re: Return URL generated in VBscript to Macro

Gysbert_Wassenaar — Thu, 15 Nov 2012 13:09:16 GMT

I've found an example for you. It shows how to create and change a variable with macros.

Re: Return URL generated in VBscript to Macro

Thu, 15 Nov 2012 13:27:25 GMT

Hi Gysbert,

Ah, the trouble is I'm developing in free version before passing onto implementation and so I don't have license, therefore can't load other people .qvm files

I've posted in another thread where I've just left this response. Can you shed any light on this?

I've progressed a bit (I think) in that I've created a couple of functions in the Edit Module screen which I think I should be able to access:

Unfortunately, one of them contains the line

set xmlobj = server.CreateObject("MSXML2.ServerXMLHTTP.3.0")

and this is bringing back the error:

Object required: 'server'

(I've tried changing to 'Server' but to no avail).

Thanks,

Jes

Re: Return URL generated in VBscript to Macro

Wed, 13 Aug 2014 16:03:03 GMT

HI,

how can I (OR CAN I...) use apicture (*.bmp or whatevert) dynamicly in ascatter chart?

Thanks