extract web information

rene_welte — Sat, 16 Nov 2024 18:47:20 GMT

Hi, Im new in qlikview and i need extract a table since this link of the Colombian Central Bank:

(if you click on the link an excel with the data will be downloaded)
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftotoro.banrep.gov.co%2Fanalytics%2Fsaw.dll%3FDownload%26Format%3Dexcel2007%26Extension%3D.xlsx%26BypassCache%3Dtrue%26path%3D%252Fshared%252fSeries%2520Estad%25C3%25ADsticas_T%252F1.%2520Promedio%2520m%25C3%25B3vil%252020%2520d%25C3%25ADas%252F1.1.TCM_%25C3%259Altimos%2520dos%2520meses%2520cargados%2520al%2520sistema%2520IQY%26lang%3Des%26NQUser%3Dpublico%26NQPassword%3Dpublico123%26SyncOperation%3D1&data=02%7C01%7Crwelte%40anasac.cl%7C2d9c65b2a2b24c0d245108d7d802cca6%7C487ca045f86943a183d6ab515aa60499%7C0%7C0%7C637215378862951003&sdata=v8IAgBC%2FB6jynXF9udrMNzznv7nDl13BmIRkxbFFgwo%3D&reserved=0

when i access to "web files" to extract the information for the first time it works fine, but when i close qlikview and open it again, can not extract the table again.

what is wrong?

this is the script:

SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='$#.##0;$-#.##0';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
SET MonthNames='ene.;feb.;mar.;abr.;may.;jun.;jul.;ago.;sept.;oct.;nov.;dic.';
SET DayNames='lun.;mar.;mié.;jue.;vie.;sáb.;dom.';[TC_ACO]:
LOAD DATE([Fecha (dd/mm/aaaa)],'DD-MM-YYYY') as Fecha,
round([Tasa representativa del mercado - TRM],0.01) as COP_USD
FROM
[https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftotoro.banrep.gov.co%2Fanalytics%2Fsaw.dll%3FDownload%26Format%3Dexcel2007%26Extension%3D.xlsx%26BypassCache%3Dtrue%26path%3D%252Fshared%252fSeries%2520Estad%25C3%25ADsticas_T%252F1.%2520Promedio%2520m%25C3%25B3vil%252020%2520d%25C3%25ADas%252F1.1.TCM_%25C3%259Altimos%2520dos%2520meses%2520cargados%2520al%2520sistema%2520IQY%26lang%3Des%26NQUser%3Dpublico%26NQPassword%3Dpublico123%26SyncOperation%3D1&data=02%7C01%7Crwelte%40anasac.cl%7C2d9c65b2a2b24c0d245108d7d802cca6%7C487ca045f86943a183d6ab515aa60499%7C0%7C0%7C637215378862951003&sdata=v8IAgBC%2FB6jynXF9udrMNzznv7nDl13BmIRkxbFFgwo%3D&reserved=0]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 55)),
Remove(Row, Pos(Top, 54)),
Remove(Row, Pos(Top, 53)),
Remove(Row, Pos(Top, 52)),
Remove(Row, Pos(Top, 51)),
Remove(Row, Pos(Top, 50)),
Remove(Row, Pos(Top, 8)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3))
));

Store [TC_ACO] into file.txt (txt);

Re: extract web information

Brett_Bleess — Thu, 14 May 2020 13:27:17 GMT

So the first thing is you did not mention the version of QlikView you are running, I would ensure you are on the latest SR (Service Release) of the major point release you are running, check the download site to be sure you have the most current SR for the major point release you are running. Supported major point releases at this point are 11.20, 12.20, 12.30, 12.40 and 12.50.

The only other workaround I have would be to use an EXECUTE statement in the script editor to run a .cmd or .bat file that would pull the file local to the machine and then you can run the load script against the local file. ROBOCOPY might potentially be another avenue as far as command to use to pull the file via the EXECUTE statement.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/Execute.htm

Sorry I do not have anything better for you, if you review the documentation for the LOAD statement and things are not working per the documentation and you are on the most current SR etc., then I would suggest you open a support case, so we can look into things further to confirm if there may be a defect in play or not.

Regards,
Brett

topic Re: extract web information in QlikView

extract web information

Re: extract web information