Identify fields common to two or more data sources.

I have a number of data sources I've been given and have the task of joining them together. Normally identifying which fields to join by would be straightforward, however the sources I have been given have over 100 million fields in some cases and one in particular having 700 million fields (I don't know how/why someone made them like this). Is there a way of identifying fields that are present in multiple sources to find potential fields to join them by?

Workflow

Join

Accepted answers

FinnCharlton

@MikeStaunton you could try using the 'Field Info' tool and joining on field name. I doubt Alteryx would get close to loading 100 million fields without crashing though, so I think your first step is going to be trying to pivot the data into something vaguely useable. How/where is the data stored at the moment?

All comments

FinnCharlton

ArnaldoSandoval

@MikeStaunton

What do you mean by 100 million or 700 million fields? are you sure they are fields or do you mean records?

Arnaldo

MikeStaunton

I mean fields. To try and identify common fields I transposed them into columns to help myself visualise them, the resulting browse tool has over 100 million records, meaning the initial input has that many fields

ArnaldoSandoval

Hi @MikeStaunton

Would it be possible for you to share two small Excel files (between 10-100 records) so somebody in the community will assist you (it is late for me, so unlikely to assist in the next 10 hours). The ones before transposing

Cheers,

Arnaldo

MikeStaunton

Fortunately for me a lot of the fields are either null or blank, which is in part the only reason I'm able to even touch the data with alteryx. The data itself is stored in tables in a data warehouse, which we then pull from as and when we need certain tables.

MikeStaunton

Unfortunately due to the nature of my work I don't think that would be possible. Thanks for the ideas though, much appreciated.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703