Full outer join and calculation

Mayot — Thu, 30 Aug 2018 10:07:42 GMT

Hello,

I have 2 buffers (tHash)

> Buffer1 : 80 lines (ID_Indicator 1000)

> Buffer2 : 77 lines (ID_Indicator 1002)

Buffer Schema :

ID1 int,

ID2 int,

ID3 int,

ID4 int,

ID_INDICATOR int,

VALUE decimal(12,2)

I have to calculate a new indicator (ID_Indicator 1003) in this way : (VALUE of ID_Indicator 1002 - VALUE of ID_Indicator 1000) = (VALUE of ID_Indicator 1003)

I have a tMap with Buffer1 in main and Buffer2 in lookup.

I join this 2 buffers with the IDs (1 to 4), except the ID_Indicator.

I do the calculation in the tMap.

My problem is that I need all the values of the buffer1 and all the values of the buffer2 for this calculation. Except that some lines have no match in the other buffer (no matter which one, I would like a solution to the following 2 cases: 1 extra line in the buffer1 without correspondence in the buffer2 or 1 more line in the buffer2 without match in the buffer1).

I think I should make a Full Outer Join, to get a value for my ID_Indicator 1003 even if it's only the value of the ID_Indicator 1000 or 1002.

When I choose Left Outer Join I have a tMap null error.

Do you have any idea on how to solve this ?

Thanks

EDIT : I found a solution when its a sum: I use a tUnite > tUniq > tAggregate (sum)
To handle substraction, I put a tMap before the tUnite with *-1 on the VALUE.

Re: Full outer join and calculation

fdenis — Thu, 30 Aug 2018 10:38:05 GMT

for full match:
using 2 new tMap, add one column name "one" value=1 then match on this column.
Add your logical into the output filer.
Full fill an other hash map with all matched ids.
add all data who did not match this ids to your output.
Good luck

topic Full outer join and calculation in Talend Studio

Full outer join and calculation

Re: Full outer join and calculation