topic Re: Joining Tables with duplicate info in QlikView

Joining Tables with duplicate info

Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Joining Tables with duplicate info

shraddha_g — Tue, 15 Nov 2016 06:50:59 GMT

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) ;

Re: Joining Tables with duplicate info

jonathandienst — Tue, 15 Nov 2016 06:51:04 GMT

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));

Re: Joining Tables with duplicate info

sushil353 — Tue, 15 Nov 2016 06:51:37 GMT

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);

Re: Joining Tables with duplicate info

Tue, 15 Nov 2016 07:08:23 GMT

Hi Jonathan

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

Any insights?

Re: Joining Tables with duplicate info

shraddha_g — Tue, 15 Nov 2016 07:10:11 GMT

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

Re: Joining Tables with duplicate info

its_anandrjs — Tue, 15 Nov 2016 07:12:38 GMT

Try this way

DB:

LOAD

FROM;

Concatenate(DB)

Family:

LOAD

FROM

Where Not Exists (ID);

Regards,

Anand

Re: Joining Tables with duplicate info

Tue, 15 Nov 2016 07:18:59 GMT

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.