topic Re: where not exists - data matching does not work in App Development

where not exists - data matching does not work

Seier-Alsenz — Mon, 11 Sep 2023 07:06:25 GMT

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

Re: where not exists - data matching does not work

Aurelien_Martinez — Mon, 11 Sep 2023 07:56:12 GMT

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

Re: where not exists - data matching does not work

Sohan_Patil — Mon, 11 Sep 2023 08:23:56 GMT

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

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;