Solved

Urgent - Getting Unique Values from 5 Fields

Posted on 2000-02-29
10
140 Views
Last Modified: 2010-04-04
Hello,

The short questions is, is there an easy way to extract the unique dates
from 5 different fields in the same record?  Here's the long version - I
hope I can articulate this so that it's easy to understand.  While importing
records from an large CSV file (called TempDB), I check each field against
existing data integrity rules before I attempt to import that record.  Once
a record is deemed 'okay', I break it up into up to three different tables:

MASTER  Applicants - (SSN is link)
  Child 1        TestData
  Child 2        SpecialNeeds

The TempDB table contains information that will be broken out into the above
three tables.  In case it isn't obvious, students (Applicants table) can
take one or more tests (TestData table), therefore, a separate record needs
to be created in TestData for each 'unique' test date.  For example, if
TempDB has these dates in the record:

DATE1            DATE2        DATE3        DATE4            DATE5
01/01/2000    12/3/1999    01/01/2000    09/27/1999    12/03/1999

In the above example, I need to create '3' new records in the TestData table
to account for the unique test dates.  Since all of the student's
information is flattened (e.g., all part of a single record) in TempDB, I
cannot perform a loop.  There will never be more that 5 test dates; however,
there may be situations where less than 5 dates exist.

Is there an easy way to extract the unique dates from 5 different fields in
the same record?  PLEASE - I need code examples.  If I had time to do research, I wouldn't have posted the question here.
0
Comment
Question by:d4jaj1
10 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2569755
what database, d4jaj?
do you mean unique per tmpdb-record?
do you mean unique per tmpdb-record and ..?
is the tmpdb a table or just a flatfile?
is it a way to perform a tmptmpdb (a query-resultset)?
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 2569845
The database part isn't really important, since at that time, it already open and I have access to the fields.  The question reads "There are 5 fields in teh TempDB tables with dates in them"  How can I compare all five fields and extract the unique dates (see grid above).  If u still think the tables matters, it's an Access 2000 DB via ODBC.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2569975
hmm, i guess i've a slow thinking ;-)

maybe you can do it with a tsstringlist like

(stringlist allways created named sl like
  sl := TstringList.Create;
  sl.Duplicates := DupIgnore;
  sl.Sorted := True;
)

now you can use this code for adding
sl.clear;
sl.Add(TempDB.FieldByName('Date1').AsString);
sl.Add(TempDB.FieldByName('Date2').AsString);
sl.Add(TempDB.FieldByName('Date3').AsString);
sl.Add(TempDB.FieldByName('Date4').AsString);
sl.Add(TempDB.FieldByName('Date5').AsString);

now you know with sl.count the records you must create
and the dates by iterating through the sl.


maybe you have to check if a date is empty like

if not(TempDB.FieldByName('Date1').IsNull) then
  sl.Add(TempDB.FieldByName('Date1').AsString);

does this help?

meikl
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Author Comment

by:d4jaj1
ID: 2570288
Yes it does, but I haven't gotten to the actual code yet.  I do try to check for nulls, but the dates are assigned to varibles instead of continually accessing the dbfield.

So, when I try this if

ssdate <> null then sl.Add(datetostr(ssdate));

The code doesn't work coorectly - meaning it tries to add ssdate and the stringlist count is 1 - even though all of the values in the five fileds are null.  I suspect that if I did this,

if not(TempDB.FieldByName('Date1').IsNull) then

It would work fine, but I need to keep the variables for other reasons.  I try

ssdate := null and/or nil

at teh beginning of the procedure in case it's never assigned, but neither work.  Nil won't compile and
null causes a run-time variant error.  ANy thoughts?

0
 
LVL 2

Expert Comment

by:kubeerja
ID: 2570398
try this code :

procedure TForm1.Button1Click(Sender: TObject);
var
 I,J,C: Integer ;
 Dates : Array [1..5] of String ;
 DateFound : Boolean ;
 s: String ;
begin
 Table1.Open ;
 while not Table1.EOF do
 begin
   for I:=1 to 5 do Dates[1]:='' ;
   Dates[1]:= Table1.FieldByName('Date1').AsString ;
   C:=1 ;
   For J:=2 to 5 do
   begin
    DateFound:= False ;
    for I :=1 to C do
    begin
       if Table1.FieldByName('Date'+IntToStr(J)).AsString=Dates[I] then
       begin
         DateFound:=True ;
         Break ;
       end ;
    end;
    if not DateFound then
    begin
      inc(C) ;
      Dates[C]:=Table1.FieldByName('Date'+IntToStr(J)).AsString ;
    end ;
   end;
   S:=EmptyStr ;
   For I:=1 to C do if trim(Dates[I])<>'' then addDateRecord ;
   Table1.Next ;
 end ;
end;
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 100 total points
ID: 2570417
well d4jaj1,

if ssDate is a typ of TDate or TDatetime,
then there must be a init.
ssdate := 0;  //somewhere in 1899
to add this type use
if ssDate <> 0 then then
  sl.Add(DateToStr(ssDate));



if ssDate is a typ of String,
ssdate := '';  //somewhere in 1899
to add this type use
if ssDate <> '' then then
  sl.Add(ssDate);


hope this helps

meikl
0
 
LVL 1

Expert Comment

by:yk030299
ID: 2571139
select DISTINCT TD from
(
select  DISTINCT DATE1 as TD from TempDB
union (select  DISTINCT DATE2 as TD from TempDB)
union (select  DISTINCT DATE3 as TD from TempDB)
union (select  DISTINCT DATE4 as TD from TempDB)
union (select  DISTINCT DATE5 as TD from TempDB)
)
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 2573391
kretzschmar ,

Thanks for the answer, the stringlist method works.  The only problem is the Date (ssDate).  I have to use a data variable because the dates in the TempDB file are broken into several fields, e.g.,

Month   Day1    Day2     Year
10       1       4        99

Unfortunately, I cannot perform an isnull because there are 4 fields to combine into a 'real' date - which is where ssDate comes from.

Now, I set all the date variables to 0 at the beginning of the code and the stringlist code works fine.  Unfortunately, I can't set ssDate back to nothing or blank.  Later in my import routine, is use

InsertRecord([keyfield, ssdate, wrdate, etc.])

to perform the insert.  Since none of the date variables are null/blank, 1899 is inserted into the table.  I know I can use NULL within InsertRecord to signify a blank field, but it doesn't allow me to test for zero 'within' the insertrecord statement.

Isn't their a way to truly convert the date variable to 'nothing', as if it was never initialized in the first place - sort of like a reset?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2573564
hi d4jaj1,

hmm, the TDateTime-Type is a Double-Type,
therefore it is not possible to null or nil it.

but you can ask for and decide like
if ssDate = 0 then
  InsertRecord([keyfield, NULL, wrdate, etc.])
else
  InsertRecord([keyfield, ssdate, wrdate, etc.]);

hope thats possible for you

meikl
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 2573796
Nope, not possible because there are 5 date variables.  Oh well, guess I need to think of a better way of doing this.

You have answered the question I asked, so thank you for your help.
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert Jpg /PNG To GIF 5 136
Best Firemonkey component pack 1 105
HTML text in the body of an email (delphi code) 12 120
Sending files from  idTcpServer Socket to idTcpClient 2 40
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question