Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Currency - store in QVD

Hi,

i'm trying to make a currency file, where I can store historical currencies.

The QVD file should grow every time I reload, if there is new data found.

I have found some code on this forum to get me started, but I'm stuck on how to store it in a QVD without getting an error.

For i = 0 to 18

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

    Calendar:

    Load

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

         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

        

    Autogenerate(1);    

    EuroRates:

    LOAD

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

     Currency,

     Code,

     [EUR/1 Unit]            as ToEUR,

     [Units/1 EUR]            as FromEUR

    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);

  

    Store EuroRates into QVD\EuroRates.QVD;

   

Next

If I let this code run, it just stops with the message "Execution of script failed. Reload old data?".

If I leave the Store EuroRates into QVD\EuroRates.QVD; line out of it, it loads currency data but only from the last date.

I'm expecting a different currency rate for each day, if it loads 18 days.

Could someone give me a hand to get this working?

Thank you!

40 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Evelien

Is the ExchangeDate field a string perhaps? Could you possibly post a copy of the qvd here?

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Jonathan,

here you have the QVD file.

The code hasn't changed since the last version we put together.

Thanks!

jonathandienst
Partner - Champion III
Partner - Champion III

Evelien

I have no problem with the Peek. Check the attached QVW.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Looking back, I omitted the quotes around the field name in the peek in my earlier post .

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Jonathan,

I changed the peek function, it works now.

Thanks a lot for all your help!

Elanir,

here, you find the code.

I didn't add the extra code you suggested. That you will have to do yourself.

The only problem left now is that the values in ToEur and FromEur are sometimes numeric, sometimes text.

I don't know what's going wrong. Can you tell me, if you are going to use the code yourself, if you also have this problem?

Thanks.

T_LastDate:

LOAD

Max(ExchangeDate) As MaxExchangeDate

From EuroRates.qvd (qvd);

Let zMaxExchangeDate = Peek('MaxExchangeDate');

DROP Table T_LastDate;

For zi = zMaxExchangeDate + 1 to Today()

    Let dd = Date(zi, '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

Concatenate (EuroRates)

LOAD * From EuroRates.qvd (qvd)

Where Not(Exists(ExchangeDate));

Store EuroRates into EuroRates.QVD;

Not applicable
Author

hi evelien,

i already managed to make it work, thank you.

attached is the currency model i built. my use of it is a little diffrent than yours since i want to hold all exchange rates from and to Euro / Dollar.

i added the peace of code i suggested so that model is updates once a day every day and gets only the new rates.

i didnt see any text / numeric problems in the result.

looking at your code, i think there is a mistake ( or somthing i dont understand)

you use 'zi' as a parameter for the loop but use ' i ' for the caculation of dates. how does ' i ' get his +1 step?

all your date and time calculations are based on '(Today()-$(i))' while the date in the html string is based on 'zi'. this will give you the right exchange rates for the wrong dates.

anyway, if you use the the "if" i suggested, the process will finish after 1 second which is good, and than if you only need a range of dates you select them in a different QVW  and in case you need different dates for other implementations, you already have your updated QVD and dont need to recreate it again.....

Anonymous
Not applicable
Author

Elanir,

I just saw the problem you discuss.

I must have changed the variables somewhere and forget to change them all.

The code did work, but didn't add new data.

I took your code and adjusted it to fit all the currencies I need.

Thanks

Anonymous
Not applicable
Author

Elanir,

I still have the problem with the numeric & the text values.

Here you see a picture.

I also put my QVD here, could you check if you see the same?

I have no idea why this happens...

My code is exaclty the same as yours, I only have more than 1 currency

QV numeric vs text.png

Not applicable
Author

could you please upload the QVW?

Anonymous
Not applicable
Author

Elanir,

here you have the QVW