where not exists - data matching does not work

[Seier-Alsenz]

Hi,

i have data sources (Table A and B). I just want to have records in table c that:
- Records from A that are not included in B
- Records from B that are not included in A

but i don't have the feeling that it works. for testing i set A=B (same data each 41 records). so i expect the result is null, 0 or error.  but the result is A concatonates B (all 82 records).

as a first solution attempt I used different qualifiers for the field ID from the different tables (like ID_tmp_V2). But this does not lead to success. as a second approach, I had provided ID with num() for safety, but that also did not lead to the result.

where is my thinking mistake?

(is there a simpler alternative to match the content of two data sources and get the inhat differences as a result?)



THE Code:


A:
LOAD
ID
FROM [lib://QVD Edited/$(vTodayBackUp2).qvd]
(qvd);

B:
LOAD
ID
FROM [lib://QVD Edited/$(vTodayBackUp2_old).qvd]
(qvd);

C:
LOAD
ID
RESIDENT A
WHERE NOT Exists(ID, 'B');

CONCATENATE (C)

C2:
LOAD
ID
RESIDENT B
WHERE NOT Exists(ID, 'A');

[Aurelien_Martinez]

Hi,

You can try to rename field

A:
LOAD
ID,

ID as ID_A
FROM [lib://QVD Edited/$(vTodayBackUp2).qvd]
(qvd);

B:
LOAD
ID,

ID as ID_B
FROM [lib://QVD Edited/$(vTodayBackUp2_old).qvd]
(qvd);

C:
LOAD
ID
RESIDENT A
WHERE NOT Exists(ID_B, ID);

CONCATENATE (C)

C2:
LOAD
ID
RESIDENT B
WHERE NOT Exists(ID_A, ID);

[Sohan_Patil · Accepted solution]

Hi,

yes, we can rename the fields and it works 

Employees:
Load *, Employee As Emp;
LOAD * inline [
Employee|ID|Salary
Bill|001|20000
John|002|30000
Steve|003|35000
New|004|35774
] (delimiter is '|');

Citizens:
Load *, Employee As Emp1;
Load * inline [
Employee|Address
Bill|New York
Mary|London
Steve|Chicago
Lucy|Madrid
Lucy|Paris
John|Miami
] (delimiter is '|');

noConcatenate
E1:
load *
Resident Employees
where not Exists (Emp1, Emp);

Concatenate

C1:
load *
Resident Citizens
where not Exists(Emp, Employee);

drop Fields Emp,Emp1 from E1;
drop Tables Employees, Citizens;