Need Help Matching data from two seperate excels with different data fields

[Rwollenschlager]

1. A list of 1,170,971 transactions with the following fields

DATE	TIME	STORE	REG	TRANSNUM	TRANSTOTAL	TENDER	AMOUNT	CCNUM	TRANSID
4/17/2019	12:26PM	1089	42	4356	[dollar figure]	VISA		223xxxxxxxxx5698	[unique identifier]

2. A list of 37,296,763 transactions with the following fields

txn_network_id	txn_txn_date	txn_txn_amt	txn_txn_card_no
[unique identifier]	20190615	[dollar figure]	[full credit/debit card number]

We need two things done. First, to identify the transactions in data set 2 that correspond to the transactions in data set 1 (presumably by comparing the Date, Amount, and first 6/last 4 digits of data set 1’s CCNUM field to the txn_txn_date, txn_txn_amt, and first 6/last 4 digits of data set 2’s txn_txn_card_no field).

Second, to dedupe the results using the full txn_txn_card_no field.

[Luke_C]

Hi @Rwollenschlager 

You would use the join tool and unique tool to accomplish this. Check out the interactive lessons if you're unfamiliar. If you have sample data available that would also be helpful to look at.

[NeilParrott]

Hi @Rwollenschlager ,

This is a very simplified dataset based on your question. The workflow creates the first6/last4 field in order to run that comparison as desired.

Hopefully this covers what you've asked for.

Good luck!

Neil

-

List before dedupe:

After dedupe: