topic Load HTML data table in QlikView

Load HTML data table

powerqlik — Mon, 28 Aug 2017 21:55:21 GMT

Hi Qlikview team, for some reason, I have a script that gets currency data from a website and for some reason, I am not able to pick up the data from the website as the script may not be correct anymore. Can anyone help modify the script to be able to pick up the right data. Below is my script. I am asking it to go and find me the CNY (Chinese Yuan) currency from the link below but for some reason, it's not picking up the data anymore.

LOAD 'CNY' as [Currency Unit],

left(@3, 7) as [Units per USD]

FROM

[http://www.xe.com/?c=CNY]

(html, codepage is 1252, no labels, table is @1) where recno() = 8;

Re: Load HTML data table

rwunderlich — Mon, 28 Aug 2017 23:27:34 GMT

xe.com prohibits automated extraction of data from their site, and they have made changes to the site to block extraction.

I'm not sure if there is another free source for CNY to USD rates.

The Qlik DataMarket provides this data for a fee. Qlik DataMarket. You may want to check that out.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Load HTML data table

powerqlik — Tue, 29 Aug 2017 00:02:27 GMT

Interesting. I can load it from here but I am not sure what the correct script would be. Can anyone modify my script to convery CNY into USD?

XE Currency Table: USD - US Dollar

Re: Load HTML data table

effinty2112 — Tue, 29 Aug 2017 08:00:46 GMT

Hi Alen,

Try this alternative:

[Conversion Rates]:

LOAD [US Dollar] as Currency,

[1.00 USD],

[inv. 1.00 USD]

FROM

[http://www.x-rates.com/table/?from=USD&amount=1]

(html, codepage is 1252, embedded labels, table is @2);

Cheers

Andrew

Re: Load HTML data table

powerqlik — Tue, 29 Aug 2017 15:58:24 GMT

Thanks Andrew but can you please help further clarify.

I believe I need to paint the whole picture for you to be able to assist me (please see below for exact script I was able to use in the past. While I am no longer able to get the data from the website shown below, this link (XE Currency Table: USD - US Dollar‌) appears to be working; however, I need help modifying my script to be able to pick up from this new link.

I have provided sample script below but in reality, I have one load for each currency, (i.e. EUR, GBP, AUD, AED, etc)

Conversion Rates:

LOAD 'JPY' as [Currency Unit],

left(@3, 7) as [Units per USD]

FROM

[http://www.xe.com/?c=JPY]

(html, codepage is 1252, no labels, table is @1) where recno() = 8;

LOAD 'HKD' as [Currency Unit],

left(@3, 7) as [Units per USD]

FROM

[http://www.xe.com/?c=HKD]

(html, codepage is 1252, no labels, table is @1) where recno() = 8;

LOAD 'CHF' as [Currency Unit],

left(@3, 7) as [Units per USD]

FROM

[http://www.xe.com/?c=CHF]

(html, codepage is 1252, no labels, table is @1) where recno() = 8;

CurrencyCountry:

LOAD * INLINE [

Currency Unit, Currency Country

CNY, China Yuan Renminbi (RMB)

EUR, Euro

HKD, Hong Kong Dollars

JPY, Japan Yen

CHF, Swiss Fran

Re: Load HTML data table

powerqlik — Tue, 29 Aug 2017 16:12:36 GMT

One quick correction I forgot to add to my script. That was my original entire script that I need help modifying to work with this website (XE Currency Table: USD - US Dollar)

CurrencyTemp:

LOAD 'CNY' as [Currency Unit],

left(@3, 7) as [Units per USD]

FROM

[http://www.xe.com/?c=CNY]

(html, codepage is 1252, no labels, table is @1) where recno() = 8;

Currency:

load

[Currency Unit],

[Units per USD],

1/[Units per USD] as [USD per Unit]

resident CurrencyTemp;

drop table CurrencyTemp;

CurrencyCountry:

LOAD * INLINE [

Currency Unit, Currency Country

CNY, China Yuan Renminbi (RMB)

EUR, Euro

HKD, Hong Kong Dollars

Re: Load HTML data table

powerqlik — Tue, 29 Aug 2017 18:19:56 GMT

The end result helps me display a table like this (please see attached).

Re: Load HTML data table

powerqlik — Wed, 30 Aug 2017 18:58:55 GMT

Hi Andrew,

I am sorry if I am repeating my question but I have shown a bit more detail below and hopefully that will allow you to help modify my script to work.