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
Not applicable
Author

why did you add this:

(qvd) Where Not(Exists(ExchangeDate)

at the end of your incremental load?

it doesnt seem right....

it looks like you are loading from the QVD only data that doesnt exist in the incremental load in in that case you will load nothing. you already took the max date as a variable and extracted only newer rates.

take that line out and i believe your problem will be solved.

Anonymous
Not applicable
Author

Elanir,

I added that piece of code because Jonathan told me so.

See his reply on this topic of the 22nd of June.

I adjusted the code and will let it run tomorrow.

I will let you know how it went.

Thanks

Anonymous
Not applicable
Author

Elanir,

It worked.

Now I see all the currencies of today & off yesterday.

Thanks a lot!

Not applicable
Author

Strange.  It works fine for me when I generate the QVD file, but reloading does not work.

Any idea what might be wrong?

2012-11-26_17-46-45.jpg

christian77
Partner - Specialist
Partner - Specialist

Don't forget to write the whole sentence

Store EuroRates into EuroRates.QVD; wrong

Store EuroRates into EuroRates.QVD (qvd); right.

Anonymous
Not applicable
Author

Thor,

I think this is what happens when you try to reload twice a day.

Because he already has all the data for the current day.

Try to wait a day so that there is new data to reload.

Then your problem should be solved..

Not applicable
Author

Beautiful

Worked like a charm.  Is there any way to generate an error message that will say something like "Exchange rates are already up to date" instead of the "Table not Found" error?

/Thor

Anonymous
Not applicable
Author

Thor,

I have no idea. Never tried it.

I don't have any problems with the error because the QVW runs automatically, only once a day.

Not applicable
Author

True.  Should not be an issue.

Final question.  If there are no exchange rates on the dates that you have transactions.  How do you select which exchange rate to use?

/Thor

Anonymous
Not applicable
Author

Thor,

I'm not using these currencies to combine with transactions.

You could try to make a loop so when there isn't a currency found for a specific day, you can let it look back one or more days, until he has found a currency.

But no idea how to actually do that.

Good luck with it!