ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Designer Discussions

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

Help needed building a crosstab or grid

jdelaguila
8 - Asteroid

Alteryx Community, 

 

I have 50 Different files that I receive on a monthly basis that contain thousands of Name's and Address's. 

 

Using a software it tells me all the duplicate records between all 50 Different Files based on Name and Address. The software creates a field called [DupeGroup] and every duplicate it finds it assigns a new number. So for example, If Joe Smith that lives in Washington DC is in 3 of the files it's puts a "1" in all 3 files in the [DupeGroup] field. The next duplicate bunch will get "2" and so on. 

 

Which leaves me with something like this:

 

FILE#DUPE GROUP
FILE 11
FILE 21
FILE 31
FILE 41
FILE 51
FILE 22
FILE 32
FILE 42
FILE 13
FILE 33
FILE 53

 

I'm trying to create a workflow that shows me how many times 1 file hit up against another file based on the  [Dupe Group] field.

 

So based on the above table the end result would be this:

FILE #FILE 1FILE 2FILE 3FILE 4FILE 5
FILE 101212
FILE 210221
FILE 322022
FILE 412201
FILE 521210

 

So using File 1 and File 5 as an example, you can see it knocked up against each other 2x - Once in the [Dupe Group]=1 and again in the [Dupe Group]=3. 

 

Any recommendations on what tool to even begin to use to get this done? I've been racking my brain over this for the last couple of days and just can't seem to find a place to start. 

 

Javier

PhilipMannering
14 - Magnetar
14 - Magnetar

Hi @jdelaguila 

 

Think I found a solution. See attached.

PhilipMannering_0-1614119852344.png

 

jdelaguila
8 - Asteroid

I don't know when, but one day when I meet you- I'm buying you dinner!!! Thank you so much!

 

Javier

Qiu
17 - Castor

@jdelaguila 

Wow, next time, lets try to answer your query.😁

Labels