Variable in URL ??

[Michiel_QV_Fan]

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.

[sparur]

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)?

[Michiel_QV_Fan]

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

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

Thanks!

[sparur]

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);

NEXT

[Michiel_QV_Fan]

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

[Michiel_QV_Fan]

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

[Michiel_QV_Fan]

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

[sparur]

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.

[Michiel_QV_Fan]

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, '<')))
));

[sparur]

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)