Load HTML data table

[powerqlik]

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;

[rwunderlich]

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

[powerqlik]

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

[effinty2112]

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

[powerqlik]

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

[powerqlik]

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

[powerqlik]

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

[powerqlik]

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.