Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?