topic Re: How To Consolidate/Match 2 Files into 1 Table in QlikView

How To Consolidate/Match 2 Files into 1 Table

haymarketpaul — Fri, 26 Jun 2015 15:12:43 GMT

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

GMC#

LastUpdatedDate

File2 Fields:

UserID

Firstname

Lastname

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?

Re: How To Consolidate/Match 2 Files into 1 Table

Gysbert_Wassenaar — Fri, 26 Jun 2015 16:33:46 GMT

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.

Re: How To Consolidate/Match 2 Files into 1 Table

swuehl — Fri, 26 Jun 2015 17:14:05 GMT

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;

Re: How To Consolidate/Match 2 Files into 1 Table

haymarketpaul — Mon, 29 Jun 2015 14:42:59 GMT

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