Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
powerqlik
New Contributor II

Load HTML data table

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;

Tags (1)
7 Replies

Re: Load HTML data table

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
New Contributor II

Re: Load HTML data table

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
Honored Contributor

Re: Load HTML data table

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
New Contributor II

Re: Load HTML data table

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
New Contributor II

Re: Load HTML data table

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
New Contributor II

Re: Load HTML data table

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

powerqlik
New Contributor II

Re: Load HTML data table

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.

Community Browser