Finding Missing Data

[bluize]

I'm seeking advice on the best way to do the following:

I have a database that has a table which contains student demographic information, i.e., Student_ID,Fname, Lname, Address1,CountyCode, City, State, Zip, etc. All total there are 25 different fields of information collected.

What I am wanting to do is to loop through the records (about 17,000) and if there is a piece of information missing (can be anything up to the 25 fields), generate a report that has the Student_ID and the name of the field that is missing data, i.e., CountyCode or Zip. Thus creating a Quality Control Report.

I hope this makes sense, if it doesn't please let me know.

Thanks in advance for any and all help.

Ben

[Keith Hubert]

Re: Finding Missing Data

Hi Ben,

Just as a matter of interest, how do you intent to fix the records with empty fields?

I assume that certain fields are not empty, like the Student_ID, Fname and Lname. So my next question is, how do you want the records presented?

eg. Browse or Report?

[bluize]

Re: Finding Missing Data

Keith, I would like a report that displays the Student_ID and any of the missing fields so that it can be passed on to the data entry team to look up and fill in the missing data.

Thanks,

Ben

[aschone]

Re: Finding Missing Data

Keith, I would like a report that displays the Student_ID and any of the missing fields so that it can be passed on to the data entry team to look up and fill in the missing data.

Thanks,

Ben

Several ways to do this. Here is one way.

Create a report based on the table in question. Drop the Student_Id field onto the report.

For the filter use the isblank() function. i.e.

isblank("student_id") .or. isblank("name") .or. isblank("address") .or. isblank("city") .or. isblank("state")...

This will give you a report listing all of the student_ids that are missing data. However this does not satisfy your condition of also indicating on the report what data is missing. For this element I would use a calculated field on the report.

for the calculated field I would use something similar to this.

vMissingDataC = if(isblank("Student_Id"),"Student Id","") + " " + if(isblank("name"),"Name","") + " " + if(isblank("address"),"Address","") + " " + if(isblank("city"),"City","") + " " + if(isblank("state"),"State","")..."

You will need to replace the field names in both the filter and the calculated field equation with your actual field names.

[Chris.Tanti]

Re: Finding Missing Data

Probably the simplest thing to try is to add a memo field and use a global update to append a flag to the memo (describing the missing field). Wipe the field first then go through each field appending the previous flag value and adding a new flag if applicable. Then just create a report that queries the memo field being populated.

The update would be something like this...

a_tbl = table.open("tbl_test")

update.fields = 4
update.field1 = "MISSING_FLAG"
update.expr1 = "''"
update.field2 = "MISSING_FLAG"
update.expr2 = "if(isblank('Fname'),'FNAME MISSING',MISSING_FLAG)"
update.field3 = "MISSING_FLAG"
update.expr3 = "if(isblank('Lname'),rtrim(Missing_Flag)+crlf()+'LNAME MISSING',MISSING_FLAG)"
update.field4 = "MISSING_FLAG"
update.expr4 = "if(isblank('Address1'),rtrim(Missing_Flag)+crlf()+'ADDRESS1  MISSING',MISSING_FLAG)"

a_tbl.update()

[Clunes]

Re: Finding Missing Data

Maybe a a simple calculated field on the report will do the trick. something like:

Case(isblank("zip"),"Zip code missing - ",isblank("Countrycode"),"country code missing - ",isblank("telephone"),"Telphone is missing") etcetc

Then you would need to adopt a method to only show those records where the the calculated field is not blank - not sure if Alpha can do that - but someone will know - 17000 records would be a big report otherwise, but I wouldn't be surprised if all 17000 had at least one missing - data entry are going to love you.

[bluize]

Re: Finding Missing Data

Thanks everyone for clues and leads, I'll give them a try and see what shakes out. You are right, the data entry people are going to "Love Me"

Ben

[Garry Flanigan]

Re: Finding Missing Data

You asked about fixing the problem and then go on to suggest a report.
Did you think of just filtering the records and stepping through them to fix obvious errors first?
Garry

[bluize]

Re: Finding Missing Data

Garry,

There are over 17,000 records in the database and there can be multiple fields that are missing data in each student record. The data comes in from an external school registration system and at the time of registration, some of the records are incomplete. My thinking was to generate a report showing what is missing and turn it over to the registration team who has gathered additional data on the students since initial registration.

Thanks,

Ben

[CALocklin]

Re: Finding Missing Data

Or use this. I haven't timed it on a really big table but 17,000 records shouldn't be a big deal. The Clients table I used it on (a long time ago) only had about 600 records.

Just put in your own table name and field names and run it.

NOTE: This one only works for character fields.

tablename = "Clients"
tp = table.open("clients")
fld_list = <<%list%
Addressf
Cityf
Statef
Zipf
Phonef
Faxf
Emailf
Trkg_Mthdf
%list%
fcnt = w_count( fld_list, crlf() )
 
ID_field = "Client_nof"
 
ndx = tp.index_primary_put("")
tp.fetch_first()    'JIC
 
msng_list = "List of Missing Data in the " + tablename + " Table as of " + dtoc(date(),"6-") + " @ " + left(time(),5) + crlf(2)
 
WHILE .not. tp.fetch_eof()
    this_line = ""
    FOR qx = 1 to fcnt
        IF eval( "tp." + word( fld_list, qx, crlf() ) ) = ""
            IF this_line = ""
                'Use this line to keep it to fewer pages.
                'this_line = trim(eval( "tp." + id_field )) + ":  " + word( fld_list, qx, crlf() )
                'Use this line to make it easier to read.
                this_line = trim(eval( "tp." + id_field )) + ":" + crlf() + space(5) + word( fld_list, qx, crlf() )
            ELSE
                this_line = this_line + ",  " + word( fld_list, qx, crlf() )
            END IF
        END IF
    NEXT
    IF this_line <> ""
        *concat( msng_list, this_line + crlf() )    'Do NOT use Concat_lines() - it's way too slow!
    END IF
    tp.fetch_next()
END WHILE
tp.close()
 
dlg_text = <<%dlg%
{ysize=.3}{units=F}{sp};
{stretch=height,width}[%mwk%.100,15msng_list];
{sp};
{stickto=bottom,center}{justify=center}<*20Close> <20Show in WordPad>
%dlg%
DIM dlg_result as C
dlg_result = ui_dlg_box( "MISSING DATA", dlg_text, <<%code%
IF a_dlg_button = "Show in WordPad"
    save_to_file( msng_list, a5.Get_Path() + "\Missing_list.txt" )
    'Note, I use WordPad to view .txt files. Default for others is usually NotePad or Word.
    sys_open( a5.Get_Path() + "\Missing_list.txt" )
END IF
%code%)

[bluize]

Re: Finding Missing Data

Thank you Cal, I'll check it out.

Ben