Not Exists/Exists Understanding - Why it is giving single result against multiple values

[erric3210]

Hi Community,

I want to understand the behaviour of Not Exists/Exits function.

If some dimension has multiple values against it then why it is returning a single value against it.

After data reload, I'm getting single frequency against 'Lucy'.

Please see this below example. Lucy has 2 addresses - 'Madrid' & 'Paris'

but as per the A-Z sorting, it is returning 'Madrid' whichever comes first.

Can someone please explain me this behaviour.

Regards,

Eric

[maxgro · Accepted solution]

Employees:

LOAD * inline [

Employee|ID|Salary

Bill|001|20000

John|002|30000

Steve|003|35000

] (delimiter is '|');  

There are 3 values in the Employee field: Bill, John, Steve

Citizens:

Load * inline [

Employee|Address

Bill |New York -->  record not loaded because the field value Bill has already been loaded into Employee

Mary|London --> loaded

Steve |Chicago --> record not loaded because the field value Steve has already been loaded into Employee

Lucy |Madrid --> loaded

Lucy |Paris --> record not loaded because the field value Lucy has already been loaded into Employee

John |Miami  --> record not loaded because the field value John has already been loaded into Employee

] (delimiter is '|')

where not Exists (Employee);

There are 5 values in the Employee field  Bill, John, Steve, Mary, Lucy,

3 record in Employees, 2 record in Citizens

Drop table Employees;

There are 2 values in the Employee field after the drop table: Mary, Lucy,

0 record in Employees, 2 record in Citizens

[erric3210]

Hi @maxgro Thanks for the detailed explanation.

Suppose, instead of address, there is Amount. So, in that case we produce the wrong result.

How we do we tackle such scenarios

Example:

Employees:
LOAD * inline [
Employee|ID|Salary
Bill|001|20000
John|002|30000
Steve|003|35000
] (delimiter is '|');

Citizens:
Load * inline [
Employee|Address
Bill|1000
Mary|2000
Steve|3000
Lucy|4000
Lucy|5000
John|6000
] (delimiter is '|') where not Exists (Employee);

Drop Tables Employees;

Output coming:

Employee Address
Lucy 4000
Mary 2000

Desired Output:

Employee Address

Lucy 4000
Lucy 5000
Mary 2000

Regards,

Eric

[vinieme12 · Accepted solution]

rename the field

Employees:
LOAD * inline [
Employee_E|ID|Salary
Bill|001|20000
John|002|30000
Steve|003|35000
] (delimiter is '|');

Citizens:
Load * inline [
Employee|Address
Bill|1000
Mary|2000
Steve|3000
Lucy|4000
Lucy|5000
John|6000
] (delimiter is '|') where not Exists (Employee_E,Employee);

Drop Tables Employees;

[erric3210]

@maxgro @vinieme12 Thanks for the clear explanation & providing the solution.

I still have some doubt related to it. For that, I'll post a new question.

Regards,
Eric