Joining Two Tables

[Vigneswaran]

Hi Team, 

I have a doubt on joining two tables and arriving at a specific resultant table. Basically all the rows from table A and table B should be in the resultant table (as the expected table below); Kindly provide hint on this workflow 

Table A

Date	Transaction ID	Tax Code	VAT amount in SAR
7/2/2023	1111222	SR	3.33
7/2/2023	1111333	SR	-7230
7/2/2023	1111444	SR	36090

Table B

Transaction ID	Invoice Number	Code	VAT amount in SAR	Status
1111222	INV001	AA	3.33	Success
1111444	INV003	AA	36090	Success
1111555	INV004	AA	-167.61	Invoiced

Expected result

Date	Transaction ID	Tax Code	VAT amount in SAR	Transaction ID	Invoice Number	Tax Code	VAT amount in SAR	Status	Difference Amount
7/2/2023	1111222	SR	3.33	1111222	INV001	AA	3.33	Success	0
7/2/2023	1111333	SR	-7230	-	-	-	-	-	7230
7/2/2023	1111444	SR	36090	1111444	INV003	AA	36090	Success	0
-	-	-	-	1111555	INV004	AA	-167.61	Invoiced	167.61

[aatalai]

@Vigneswaran join based Transaction ID then union the tthree outputs of the join (based on field name)

Let me know if that works

[binuacs]

@Vigneswaran similar to @aatalai approach

[Vigneswaran]

Thank you! Much appreciated!

But, the Transaction ID 1111555 is not present in Table A, only available in Table B. So in the resultant table, The Date (Column 1), Tax Code (Column 3), and VAT Amount in SAR (Column 4) should be NULL and the rest all fields should have the value from Table B.  

I'm not understanding how VAT amount in SAR (Table 1 - left table) has the value of -167.61 and the actual right table (table 2) column "Right_VAT amount in SAR" is not having a value 

please clarify

[aatalai]

@Vigneswaran the union is there to sort out for the ids not being present in both tables

[binuacs]

@Vigneswaran updated the workflow according to the requirement, note that Alteryx does not support the same column name for different fields