Join problem

[#1]

Hi

I want to join two tables, and insert the new calculate data on the first one, look at the example:

Table1:

Measure	Value	Date
Units	1.00	1
Dollar	25.00	1
Units	1.00	2
Dollar	25.00	2

Table2:

Date	Rate
1	1.05
2	1.09

I would like to include on the Table 1, this lines that means (Dollar: Value * Rate on the same Date)

Measure	Value	Date
LocalValue	26,25	1
LocalValue	27,25	2

I'm try to do like this, but I continue have a problem:

JOIN (Table2)

LOAD

  'LocalValue' as [Measure],

  [Value]*[Rate] AS [Value]

RESIDENT Table1

WHERE [Measure] = 'Dollar'

but I'm geting this error message:

Error Field not found - <Rate>

What I'm doing wrong?

--- EDITED

Example here:

Table1:

Load * inline

[

Measure,Value,Date

Units,1,1

Dollar,25,1

Units,1,2

Dollar,25,2

];

Table2:

Load * inline

[

Date,Rate

1,2

2,3

];

Table1:

JOIN (Table2)

LOAD

  'LocalValue' as [Measure],

  [Value]*[Rate] AS [Value]

RESIDENT Table1

WHERE [Measure] = 'Dollar'

[prieper]

Not quite clear on the difference in line 1 and 2 resp. 3 and 4 in the first table. Please explain.

If there is a 1:1 ratio, would recomment to use a mapping:

mapRate: MAPPING LOAD Date, Rate FROM ....

Data:

LOAD

     *,

    Value * APPLYMAP('mapRate', Date)          AS Local

FROM

     ....;

If there are multiple currencies involved, you may consider a combined key.

Else check on the INTERVALMATCH-function

HTH Peter

[sunny_talwar]

May this:

Table1:

Load * inline

[

Measure,Value,Date

Units,1,1

Dollar,25,1

Units,1,2

Dollar,25,2

];

Table2:

Load * inline

[

Date,Rate

1,2

2,3

];

Join (Table1)

LOAD

  'Dollar' as [Measure],

  Date,

   Rate

RESIDENT Table2;

Table:

LOAD *,

  Value * Rate as [Total Value]

Resident Table1;

DROP Tables Table1, Table2;

Output:

Attaching the sample application for ease of understanding.

HTH

Best,

S

[maxgro]

Table1:

Load * inline [

Measure,Value,Date

Units,1,1

Dollar,25,1

Units,1,2

Dollar,25,2

];

Table2:

Load * inline [

Date,Rate

1,1.05

2,1.09

];

left join (Table2) load

'LocalValue' as Measure,

Value,

Date

Resident Table1

Where Measure = 'Dollar';

Concatenate (Table1)

load Measure, Value*Rate as Value, Date

Resident Table2;

DROP Table Table2;

[Anonymous]

Hugs