How to read latest exchange rates

[#1]

Hi all, Please help

I have a currency table which has a key and currency code something like

Currency Key    Code

1                       USD

Similarly I have Exchange Table which has exchange rates and Currency Key , lastupdated date and other fileds

Currency Key                    Rate                            LastUpdate

4                                      0.73                             2014-07-19 16:00:50

4                                      0.69                             2014-07-19 14:00:30

and so on

Now, I need to read the last updated rate for a particular currency key and match it agianst the currency code.

I am able to read last updated date data for example when I say Max date, I get multiple records which are updated on the same day at different times, I need to get only  one latest updated record for each currencykey with last updated date and time, for example for currencykey 4 as shown above. i need to only the record updated at 2014-07-19 16:00:50

Could someone help me on how to go about it please?

Thanks in advance

[datanibbler]

Hi,

using the max() function you should be able to get only the latest record - you just have to use the complete timestamp (including date and time) within the max() to get the time that currency_code was last updated.

That is an aggregation function, so you'll need a GROUP BY clause which will probably give you just what you want - by grouping by currency_code, you get the date_time any of the codes was last updated.

[MK_QSL · Accepted solution]

=Aggr(TimeStamp(Max(LastUpdate)),[Currency Key])

[#4]

Hi Manish,

Thanks for the reply,

I now get only one value for each currency key except, the FX Rate is not the latest Last Updated Date, but it is giving me very earliest values , the very beginning record for each currency key in the database, could you please help me on how to fix this?

Thanks,

[Anonymous]

Maybe something like this in the script ?

Temp :

LOAD

[Currency Key],

Rate,

timestamp([Last update]) as [Last update]

INLINE [

    Currency Key, Rate, Last update

4, 0.73, 2014-07-19 16:00:50

4, 0.69, 2014-07-19 14:00:30

6, 0.75, 2014-07-20 16:00:50

6, 0.66, 2014-07-20 14:00:30

]

;

Data :

load

  *

where Latest = 1

;

load

    if ( previous ( [Currency Key] ) = [Currency Key] , 0 , 1 ) as Latest ,

    [Last update] ,

    [Currency Key],

    [Rate]

resident Temp

order by [Currency Key] desc

;

drop table Temp ;

[#6]

Hi Bill,

Thanks for the reply. There are a lot of records to be able to specify in the script it might be inefficient to do so.

Thanks

[Anonymous]

Give it a go, you may be pleasantly surprised.

[Anonymous]

..or try it this way :

Temp :

LOAD

[Currency Key],

Rate,

timestamp([Last update]) as [LastUpdate]

INLINE [

    Currency Key, Rate, Last update

4, 0.73, 2014-07-19 16:00:50

4, 0.69, 2014-07-19 14:00:30

6, 0.75, 2014-07-20 16:00:50

6, 0.66, 2014-07-20 14:00:30

]

;

Data :

load

    [Currency Key],

    timestamp(max([LastUpdate])) as [LastUpdate]

resident Temp

group by [Currency Key]

order by [Currency Key]

;

left join

load

*

resident Temp

;

drop table Temp ;

[#9]

Hi Bill,

I tried similar one with inner join function, it is not working for both, I am still seeing the records from 2012,2013, and so on the function is not evaluating for the timestamp(max(date))

[Anonymous]

Oh dear, sorry it does not work for you. 

It does work fine for me though.

Hopefully somebody else will suggest something that does work for you.