topic Variable in URL ?? in QlikView

Variable in URL ??

Michiel_QV_Fan — Tue, 29 Jun 2010 09:52:49 GMT

Hi all,

I'm extracting data from text on URL's. For instance, this URL.

(http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IFRIESLA37&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&yearend=2010&format=1)

I want to extract a few 1000 different datasets, all based on this URL. The only thing that changes is the part "ID= ".

I would like to use 'for each next' or something like that to have the ID part variable so I only have 1 line of code.

Is this possible?

Your help is much appreciated.

Variable in URL ??

sparur — Tue, 29 Jun 2010 10:11:19 GMT

Hello Michiel.

What part of your URL will be change?

Only "ID=IFRIESLA37" and other part (&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&) will not be change or all string after "ID="?

what values you want to get in your cycle (For Each Next)?

Variable in URL ??

Michiel_QV_Fan — Tue, 29 Jun 2010 10:18:25 GMT

Yes, only the part after the = symbol will change. Nothing else.

The cycle includes for instance IFRIESLA38, IFRIESLA39, INOORDBR6 etc. etc.

Thanks!

Variable in URL ??

sparur — Tue, 29 Jun 2010 10:22:31 GMT

so I think you should use something like this:

For EACH vID IN 'IFRIESLA38', 'IFRIESLA39', 'INOORDBR6'

LET vURL = 'http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=' & $(vID) & '&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&';

LOAD *

FROM $(vURL);

Variable in URL ??

Michiel_QV_Fan — Tue, 29 Jun 2010 10:36:59 GMT

I get errors from this script. The & and ' are present in the url on which it stops.

For EACH vID IN 'INOORDBR15', 'INOORDBR6'

LET vURL = LET vURL = 'http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID='&$(vID)&'&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '<')))
));

LOAD *

FROM $(vURL);

NEXT

Variable in URL ??

Michiel_QV_Fan — Tue, 29 Jun 2010 10:43:44 GMT

Update script:

For EACH vID IN 'INOORDBR15', 'INOORDBR6'

LET vURL = LET vURL = 'http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID='&$(vID)&'&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&&yearend=2010&format=1'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '<')))
));

LOAD *

FROM $(vURL);

NEXT

Variable in URL ??

Michiel_QV_Fan — Tue, 29 Jun 2010 11:47:16 GMT

I've created this script:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ #.##0,00-';
SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';

For EACH vID IN 'INOORDBR15', 'INOORDBR6'

SET vURL1 = http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=;
LET vURL2 = $(vID);
SET vURL3 = &'&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&&yearend=2010&format=1'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '<')))));

LOAD *

FROM '$(vURL1)'&'$(vURL2)'&'$(vURL3)';

NEXT

I see in debug that all the statements are correct and combined they should work, but the combining doesn't go right. Can you help me with thios?

Thanks

Michiel

Variable in URL ??

sparur — Tue, 29 Jun 2010 11:50:34 GMT

I tried to load data (http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=INOORDBR6&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&&yearend=2010&format=1) through web files wizard. I can't do it 😞

I think that problem in your html code. I don't sure but I think that you should have special tag <TABLE> in your html page.

Variable in URL ??

Michiel_QV_Fan — Tue, 29 Jun 2010 11:55:16 GMT

How about this one?

LOAD Date,
TemperatureHighC,
TemperatureAvgC,
TemperatureLowC,
DewpointHighC,
DewpointAvgC,
DewpointLowC,
HumidityHigh,
HumidityAvg,
HumidityLow,
PressureMaxhPa,
PressureMinhPa,
WindSpeedMaxKMH,
WindSpeedAvgKMH,
GustSpeedMaxKMH,
[PrecipitationSumCM<br>]
FROM
[http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=INOORDBR15&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&yearend=2010&format=1]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '<')))
));

Variable in URL ??

sparur — Tue, 29 Jun 2010 12:19:00 GMT

I get error message:

Cannot open file
LOAD Date,
TemperatureHighC,
TemperatureAvgC,
TemperatureLowC,
DewpointHighC,
DewpointAvgC,
DewpointLowC,
HumidityHigh,
HumidityAvg,
HumidityLow,
PressureMaxhPa,
PressureMinhPa,
WindSpeedMaxKMH,
WindSpeedAvgKMH,
GustSpeedMaxKMH,
[PrecipitationSumCM<br>]
FROM
[http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=INOORDBR15&graphspan=custom&month=1&day=1&year=1996&monthend=12&dayend=31&yearend=2010&format=1]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '<')))
))

but may be it depend from our corporative firewall and proxy server. I don't know how I can setup proxy settings in QlikView Developer. So I can check your script later (from my home computer)

Variable in URL ??

sparur — Tue, 29 Jun 2010 18:41:03 GMT

Michiel, as I promised

I checked it and fixed it 🙂

see my example in attachment...

Variable in URL ??

Michiel_QV_Fan — Wed, 30 Jun 2010 07:29:29 GMT

Absolutely GREAT!!!!!!!!!!!!!!!!!!

Thanks for your help!!

Variable in URL ??

Michiel_QV_Fan — Mon, 19 Sep 2011 13:54:17 GMT

I'm now using Qlikview 10.

The set url variables aren't needed at all.

Just replace the URL parts with variables and Qlikview creates the correct URL's.

FROM[http://www.blablablabla$(variable)blablalbalbalbla$(variable2)blablabla.html]

Works great.