Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

evelien_morel
New Contributor

URL load - text vs numeric

Hi,

I'm loading data from a currency website.

Some of the retrieved data is numeric, other is text. I have no idea why this happens.

Specificly, it occurs in the fields ToEUR & FromEUR

Does anyone have an idea how to fix this?

Thanks

Here is the code i'm using:

For i = 0 to 16

    Let dd = Date(Today()-$(i), 'MM/DD/YY');

    

    EuroRates:

    LOAD

     Date(Today()-$(i))                                                                           As ExchangeDate,

     Currency,

     Code,

     [EUR/1 Unit]                                                                                  as ToEUR,

     [Units/1 EUR]                                                                                 as FromEUR,

      Year(Today()-$(i))                                                                           As Year,

     Month(Today()-$(i))                                                                          As Month,

     Day(Today()-$(i))                                                                             As Day,

     Week(Today()-$(i))                                                                           As Week,

     WeekYear(Today()-$(i)) & '-' & Num(Week(Today()-$(i)),'00')                 As RollingWeek,

     Weekday(Today()-$(i))                                                                       As WeekDay,

     Date(MonthStart(Today()-$(i)),'YYYY-MM')                                         As RollingMonth,

        Right(year(Today()),2) & '-Q' & ceil(Month(Today())/3)                      as [YearQtr],

     'Q' & Ceil(Month(Today()-$(i))/3)                                                        As Quarter

  

   

    FROM

    [http://www.oanda.com/convert/fxdaily?date=$(dd)&date_fmt=us&exch=EUR&lang=en&sel_list=USD_HUF_UAH_RU...]

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

Next

Store EuroRates into EuroRates.QVD;

Here you see a picture of the result:

QV numeric vs text.png

Tags (2)
3 Replies
Not applicable

Re: URL load - text vs numeric

Hey, i would like to convert dollars to Brazilian currency (Real)... Can you help me out in load something like this, loading all data since 2008 until now?

sureshbaabu
Contributor III

Re: URL load - text vs numeric

Hello,

I believe it is read as string. please try to convert it from string to number using num() function.

on the load Script page: Num(Column_name) as Column_name

Hope it Helps!!!

Thanks.

MVP & Luminary
MVP & Luminary

Re: URL load - text vs numeric

Hi Evelien,

this seems to work:

...

Num(Num#([EUR/1 Unit], '#.0#', '.', ','))                                                                              as ToEUR,  
Num(Num#([Units/1 EUR], '#.0#', '.', ','))                                                                             as FromEUR, 

...

- Ralf