Compare two tables to find differences at row level

[alec1982]

hi-

need your thoughts on comparing 2 tables to find if all data at row level matches. the tables has 5 columns that represent Invoice Number, Date, Amount, Qty, Customer Name.

Need a way to compare the 2 tables and lets say in a row the customer name doesnt show on one of the tables then I want to show that in a new column indicating "Customer Name not match" similarly if there is more than one column that dont match.. for example "Customer Name", "Qty" dont Match.

any one has a handy logic for this request?

[Or]

If it is possible for any of the fields to not match, how can you even tell they're supposed to be the same row?

If you know Invoice Number is always a match, for example, you can just concatenate the two tables, use invoice number as your dimension or group by field, and then create both a Count(Distinct Field) and a regular Count(Field) on each field. If your count distinct is 2 the values don't match. If your regular count is exactly 1 then one of the values is missing (null). If values can't be null, you can just use the count distinct.

[alec1982]

thanks for the quick response. good approach but doesnt resolve it as I want this to be on the script side. and Yes, invoice number is the key.

[pravinboniface · Accepted solution]

How about something like this.  It will work if you have a match on invoice in both tables.

Another option under the same conditions would be to build a series of MAPPING LOADS for each column using the master table, for example (Invoice & Date),  (Invoice & Amount), (Invoice & Qty), (Invoice & Name).  Then when you read the 2nd table, you can use ApplyMap() with an if condition to see if the values match and build a status column.

tab1:
NoConcatenate
Load  [Invoice Number], Date, Amount, Qty, [Customer Name]
inline [
Invoice Number, Date, Amount, Qty, Customer Name
1,2,3,4,5
6,7,8,9,10
];

join (tab1)
Load  [Invoice Number], Date1, Amount1, Qty1, [Customer Name1] inline [
Invoice Number, Date1, Amount1, Qty1, Customer Name1
1,2,3,4,5
6,11,12,13,14,15
];

tab2:
NoConcatenate
load [Invoice Number], Date, Amount, Qty, [Customer Name],
if (Date <> Date1, 'Date does not match;','') &
if (Amount <> Amount1, 'Amount does not match;','') &
if (Qty <> Qty1, 'Amount does not match;','') &
if ([Customer Name] <> [Customer Name1], 'Amount does not match;','') as status
resident tab1;
drop table tab1;

exit Script;

[Or]

It works perfectly fine in script, you just use a group by instead of a dimension.

[alec1982]

thank you for your thoughtful and easy solution.