Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Michiel, as I promised
I checked it and fixed it 🙂
see my example in attachment...
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)?
Yes, only the part after the = symbol will change. Nothing else.
The cycle includes for instance IFRIESLA38, IFRIESLA39, INOORDBR6 etc. etc.
Thanks!
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
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
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
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
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.
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, '<')))
));
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)