ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Cross Reference 2 files with No matching columns

katonyaj
6 - Meteoroid

Hi,

 

I need to somehow combine 2 completely different files of the same data to find out a true number of transactions that occurred. I've attached 2 sample files as well. 

 

I am trying to find the Invoice # and Amounts (Column K & M) from Report 1 on Report 2 (Column L) to show that the 2 systems are syncing.

 

What I have been able to figure out is Column K in Report 1 agrees to Column L in report 2. The problem is the wording or cadence is not always an exact match. There may be some letters missing at the end but is probably at least an 80% match. (See example screenshots below)

 

katonyaj_0-1619812289025.png

 

katonyaj_1-1619812289037.png

 

 

One particular one (Sprocket), I have only been able to cross reference to report 2 by Ledger Debit or Credit Amount and Date seems like.

 

Not sure where to even go next on this one. 

 

Please help!

 

 

 

 

Gina2021
8 - Asteroid

@katonyaj it looks like you have a REGEX need and I look forward to learning along side you!

 

To get started, I put both reports into a textbox input so it can be within the workflow, and filtered the columns you identified down to contains "crown".  I find that limiting things down helps me see my problem more clearly.  Here's a snippet and starter workflow for the community to take over.

 

Gina2021_0-1619813553666.png

 

 

 

Qiu
17 - Castor

@katonyaj 

I had to exclude the particular one (Sprocket) first.

As a start, I am using equal or contain one by the other function, maybe it will work for you?

0501-katonyaj.PNG

Gina2021
8 - Asteroid

That's very cool!

Qiu
17 - Castor

@Gina2021 

Glad to help and thank you for the accept mark. 😁

Labels