Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

General Discussions

Discuss any topics that are not product-specific here.

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

Rwollenschlager
5 - Atom
  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]

 

  1. 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.

 

2 REPLIES 2
Luke_C
17 - Castor
17 - Castor

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
8 - Asteroid

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:

NeilParrott_0-1650905546834.png

 

 

After dedupe:

NeilParrott_1-1650905560592.png

 

Labels
Top Solution Authors