Joining Tables with duplicate info

[#1]

Hi

I am trying to concatenate 2 tables with staff information into one. DB is the employee's information, whilst Family is the employee's family member details.

The "DB" table will be the 'master' table. There can be multiple 'ID' sharing the same 'RegNo'

An employee can have details in both tables. If such a scenario exists, I would like to only retain information in the DB table and not load this particular employee's details from the Family table.

Otherwise, all other entries in the Family table should be added.

I have tried the following but the Family data does not seem to be loaded into the DB table.

DB:

LOAD [ID],

     RegNo,

     Name,

     Gender,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation

FROM ...;

NoConcatenate

Family:

LOAD [ID],

     RegNo,

     Name,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation

FROM ...;

Concatenate(DB)

LOAD *

Resident Family

Where Not Exists (ID);

Drop Table Family;

[shraddha_g]

make sure that format of ID in both table should be same.

Try:

DB:

LOAD [ID],

     RegNo,

     Name,

     Gender,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation

FROM .DB..;

Concatenate

LOAD [ID],

     RegNo,

     Name,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation

FROM .FAMILY..

where Not Exists (ID, ID) ;

[jonathandienst · Accepted solution]

Concatenate directly. Your second load means all IDs exist, so Not(Exists()) is always false.

DB:

LOAD [ID],

     RegNo,

     Name,

     Gender,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation,

     'Employee' as Type

FROM ...;

Concatenate (DB)

Family:

LOAD [ID],

     RegNo,

     Name,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation,

     'Family' as Type

FROM ...

Where Not(Exists(ID));

[sushil353]

Hi,

Try This:

DB:

LOAD [ID],

     RegNo,

     Name,

     Gender,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation

FROM ...;

Concatenate(DB)

LOAD

[ID],

     RegNo,

     Name,

     POSTCODE,

     AGE as Age,

     Role,

     Occupation

From ....family

Where Not Exists (ID);

[#5]

Hi Jonathan

Gave your code a go, but it is not loading data from the Family table.

Any insights?

[shraddha_g]

Check formats of ID in both tables? does it match?

[its_anandrjs]

Try this way

DB:

LOAD

    *

FROM;

Concatenate(DB)

Family:

LOAD

   *

FROM

Where Not Exists (ID);

Regards,

Anand

[#8]

Hi Shraddha

Found out my error. I had changed the field names (from EmployeeID to ID) when loading the data, and this caused the problem.

Using "Where NOT Exists (ID, FamilyID)" solved the issue.