Retrieving and Combining Fields from Excel Tables

[#1]

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;

[#2]

The script below is running okay.

EXCHRATE_Temp:

LOAD ExchDate

FROM

..\Excel\Calendar.xls

(biff, embedded labels, table is Calendar$);

JOIN LOAD ExchDate,

CurrencyKey,

Curr9.ExchRate

FROM

..\Excel\Curr9.xls

(biff, embedded labels, table is Curr9$);

JOIN LOAD ExchDate,

CurrencyKey,

Curreg9.ExchRate

FROM

..\Excel\Curreg9.xls

(biff, embedded labels, table is Curreg9$);

EXCHRATE:

LOAD

*

, If(Date([ExchDate])>=Date('01/01/2010') And Date([ExchDate])<=Date('05/01/2010'), [Curreg9.ExchRate], [Curr9.ExchRate]) AS MyRate

RESIDENT EXCHRATE_Temp WHERE [CurrencyKey] <> 0;

DROP TABLE EXCHRATE_Temp;