Transfer value from one table to another table

[reporting_neu]

Hello together,

I'm just at a loss and unfortunately can't find a solution.

I have two tables with identical column names and would like to join/concatenate them.

I also need the amount from TMP2 in TMP1. The connection should be made via ReceiptYear and Part.

The data looks like this:

TMP1:

ReceiptYear	Part	Amount	Flag
2021	1002	100	E_BelegPos
2021	2000	17	E_BelegPos
2022	2500	180	E_BelegPos

TMP2:

ReceiptYear	Part	Amount	Flag
2021	1002	300	V_BelegPos
2021	2000	29	V_BelegPos
2022	2500	12	V_BelegPos

The result should be the following:

ReceiptYear	Part	Amount - E_BelegPos - Sum({Flag = {'E_BelegPos'}>}Amount)	Amount -V_BelegPos - - Sum({Flag = {'V_BelegPos'}>}Amount
2021	1002	100	300
2021	2000	17	29
2022	2500	180	12

I wrote the following script:

What am I doing wrong? 🤔

[Lakshminarayanan_J · Accepted solution]

Hi @reporting_neu ,

Please find the below details , whether it is matching with your expectation.

Try using Join .

I have created the inline table based on the data you have provided as below 

Test1:
LOAD * Inline [

ReceiptYear , Part , Amount

2021 , 1002 , 100

2021 , 2000 , 17

2022 , 2500 , 180

];
Join
Test2:
LOAD * Inline [
ReceiptYear ,Part, Amount2
2021, 1002, 300
2021, 2000, 29
2022, 2500, 12
];

Result:

[marcus_sommer]

Where is the problem? You concatenate both tables - so you will have all information within one table. By using sum(Amount) you get the combined sum and with adding the above shown set analysis you could differ between sources and displaying only one of them or the difference / rate of both - so everything should be working.

- Marcus