Hi guys,
I need help to solve the following situation.
I have three tables (In Excel):
CALENDAR:
ExchDate |
01/01/2010 |
02/01/2010 |
03/01/2010 |
04/01/2010 |
05/01/2010 |
06/01/2010 |
07/01/2010 |
08/01/2010 |
09/01/2010 |
10/01/2010 |
11/01/2010 |
12/01/2010 |
13/01/2010 |
14/01/2010 |
15/01/2010 |
CURR9:
ExchDate | CurrencyKey | Curr9.ExchRate |
10/01/2010 | -2 | 3.8 |
10/01/2010 | 29 | 5.2 |
CURREG9
ExchDate | CurrencyKey | Curreg9.ExchRate |
01/01/2010 | -2 | 3.71 |
02/01/2010 | -2 | 3.72 |
03/01/2010 | -2 | 3.73 |
04/01/2010 | -2 | 3.74 |
05/01/2010 | -2 | 3.75 |
01/01/2010 | 29 | 5.01 |
02/01/2010 | 29 | 5.02 |
03/01/2010 | 29 | 5.03 |
04/01/2010 | 29 | 5.04 |
05/01/2010 | 29 | 5.05 |
I need to create a field (which I will be using several times) combining fields of the tables above.
Below the script I am trying to run.
Thanks in advance for any help,
Aldo.
CALENDAR:
LOAD Date(ExchDate) AS ExchDate
FROM
.. \Excel\Calendar.xls
(biff, embedded labels, table is Calendar$);
CURR9:
LOAD ExchDate,
CurrencyKey,
Curr9.ExchRate
FROM
.. \Excel\Curr9.xls
(biff, embedded labels, table is Curr9$);
CURREG9:
LOAD ExchDate,
CurrencyKey,
Curreg9.ExchRate
FROM
.. \Excel\Curreg9.xls
(biff, embedded labels, table is Curreg9$);
EXCHRATE_Temp:
LOAD *,1 RESIDENT CALENDAR;
LEFT JOIN (EXCHRATE_Temp) LOAD * RESIDENT CURREG9;
LEFT JOIN (EXCHRATE_Temp) LOAD * RESIDENT CURR9;
EXCHRATE:
LOAD
*
, If([ExchDate]>=Date(01/01/2010) And [ExchDate]<=Date(05/01/2010), [Curreg9.ExchRate], [Curr9.ExchRate]) AS [rate]
RESIDENT EXCHRATE_Temp;
DROP TABLE CURR9;
DROP TABLE CURREG9;
DROP TABLE EXCHRATE_Temp;
