AnsweredAssumed Answered

Similar Fields with bad data, trying to combine the two

Question asked by lrodri007 on Jun 30, 2017
Latest reply on Jul 5, 2017 by lrodri007

The premise:

I have a database dump that I threw into Filemaker (running new 16 version).  It has 3 tables. 

1. Participant List Records,  18000 records, with about 100 fields (many reiterate, see below)

2. Program List Records, it has about 45 records

3. Join Table PxP (participant program relationship), it has about 50,000 records

 

My question relates to the many similar fields that were created as different people administered the database itself in Participant Table.

For example there are many fields that are duplicated; i.e. Gender01, Gender02, Gender03.  Each field has similar data, Male, Female although they are upper case in one field and lower case in another.  As I scroll through the data, you can see data in some columns, then goes blank while another column begins to show data.  Visually you can see time working here.  Gender01 shows data for several records then stops as records begin to show in Gender02.  And every once and a while you see both fields filled in.

 

I am trying to export this data into concise excel file for management to work with (they work with Pivot Tables in excel).  Instead of exporting data as is I'm trying to see if I can consolidate the common columns into one.

 

I want to go through all the different common fields (Gender, Ethnicity, Race, etc.) arbitrarily (actually choose the column with majority of data) choose a master column that I want to keep then....

1. Run process to compare values of common fields, Gender1:Gender2:Gender3 etc

2. If master column has data keep going

3. If Master column is blank look through other fields to see if they have data to copy over to first column.  In this scenario, If I get hit on Gender2 stop, otherwise if Gender 2 is also blank check 3, 4, 5 for data until all possibilities are exhausted.  If blank at the end oh well....

4. That's about it, once done, I will do quick check then delete all extra columns. 

 

In the end I want to go from :

LName:FName:Gender1:Gender2:Ethinity1:Ethnicity2:zipcode

to

LName:Fname:Gender:Ethnicity:Zipcode

 

Thanks for any help..... hopefully there are solutions out there other than walk away

Outcomes