Conversion rates expression

[#1]

Hi Forum,

I've a straight table like below,        

Project	Employee	WorkDate	WorkHours	BaseCurrency	EuropeExpense	DenmarkExpenses	HungaryExpenses
100	ABC	12-04-2016	6	EUR	1200	0	0
100	ABC	13-04-2016	7	EUR	1400	0	0
100	XYZ	12-04-2016	5	DKK	0	500	0
100	XYZ	13-04-2016	8	DKK	0	800	0
100	ABC	14-04-2016	8	EUR	1600	0	0
100	OPQ	14-04-2016	9	HUF	0	0	1800

and now my client wants like below,

Project	Employee	WorkDate	WorkHours	BaseCurrency	TransactionRate	EuropeExpense	DenmarkExpenses	HungaryExpenses	USDExpense
100	ABC	12-04-2016	6	EUR	NA	1200	0	0	1200*15 = 18000
100	ABC	13-04-2016	7	EUR	NA	1400	0	0	1400*15 = 21000
100	XYZ	12-04-2016	5	DKK	NA	0	500	0	500*0.8 = 400
100	XYZ	13-04-2016	8	DKK	0.8	0	800	0	800*0.8 = 640
100	ABC	14-04-2016	8	EUR	15	1600	0	0	1600*15 = 24000
100	OPQ	14-04-2016	9	HUF	0.2	0	0	1800	1800*0.2 = 360

where i need to write an expression for USDExpense and transaction rates given to convert other expenses(EUR, DKK and HUF) into USD.

Since TransactionRate is Null or Notavailable for first record(BaseCurrency-EUR)(workdate: 12-04-2015), USDExpenses expression should pick latest EUR TransactionRate that is available i.e 15(record 5) and same case for other Employees belongs to different countries having different currencies and rates.

please suggest how to achieve USDExpenses expression?

Thanks in advance,

Narsi

[sunny_talwar]

You want to do this in the script or directly on the front end of the application? Where is TransactionRate coming from and where are the defauly values for TransactionRate coming from?