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