How To Consolidate/Match 2 Files into 1 Table

[haymarketpaul]

I have 2 files of data that i need to consolidate together.

The same user may or may not exist in both files.

File1 Fields:

CompID

Firstname

Lastname

Email

GMC#

LastUpdatedDate

File2 Fields:

UserID

Firstname

Lastname

Email

GMC#

LastUpdatedDate

GMC# is unique to a user

The UserID & CompID (autonumbers from different systems) are not compatible so i need to match using the other fields and where they exist in both files keep the one with the highest LastUpdatedDate

Some fields are empty (eg Email and/or GMC# missing)

Is there a best way to go about this?

Using Exists maybe?

[Gysbert_Wassenaar]

This will join the two tables on the common fields.

Table:

LOAD

CompID,

Firstname,

Lastname,

Email,

GMC#,

LastUpdatedDate

FROM ...

INNER JOIN

LOAD

UserID,

Firstname,

Lastname,

Email,

GMC#,

LastUpdatedDate

FROM ...

But if for example Email is empty for a particular GMC# one table and not in the other table for the same GMC# then the records will be discarded.

[swuehl]

You can create a key out of the fields that must exist in both tables and identify the user.

As I read you post, these fields are Firstname and Lastname only (at least in this sample).

INPUT:

LOAD

     Autonumber( Firstname & Lastname) as Key,

     Firstname,

     Lastname,

     Email,

     GMC#,

     LastUpdatedDate

FROM File1;

//Table gets autoconcatenated to INPUT, same fields used

LOAD

     Autonumber( Firstname & Lastname) as Key,    

     Firstname,

     Lastname,

     Email,

     GMC#,

     LastUpdatedDate

FROM File2;

CONSOLIDATED:

LOAD

     Key as KeyCheck,

     Firstname,

     Lastname,

     Email,

     GMC#,

     LastUpdatedDate

RESIDENT INPUT

WHERE NOT EXISTS(KeyCheck, Key)

ORDER BY KEY, LastUpdateDate desc;

DROP TABLE INPUT;

[haymarketpaul]

That's great thank you - i think i can adapt that to suit my needs (which are a bit more complex than my sample)