Joining Two Tables

Question

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

DateTransaction IDTax CodeVAT amount in SAR7/2/20231111222SR3.337/2/20231111333SR-72307/2/20231111444SR36090

Table B

Transaction IDInvoice NumberCodeVAT amount in SARStatus1111222INV001AA3.33Success1111444INV003AA36090Success1111555INV004AA-167.61Invoiced

Expected result

DateTransaction IDTax CodeVAT amount in SARTransaction IDInvoice NumberTax CodeVAT amount in SARStatusDifference Amount7/2/20231111222SR3.331111222INV001AA3.33Success07/2/20231111333SR-7230-----72307/2/20231111444SR360901111444INV003AA36090Success0----1111555INV004AA-167.61Invoiced167.61

binu_acs · Answer

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

join_300124.yxmd

aatalai · Answer

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

Vigneswaran · Answer

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