Concanate tables with conditions

[MEBG93]

Hello experts!

I'm kind of stuck on this one. I have two tables (excel and database) and I need to concatenate them with this conditions in mind:

-The excel table remains the same no matter what.

-The database values has to be concatenated only if IDs values are not in excel. (I tried to do this with where not exists(ID) but it only worked when ID matched in excel; for the non matching values, it only retrieved the first row that didn't match the condition, so most of data was lost).

tables are:

excel:

load * inline [

TYPE, ID, LOCATION, PK

User, 1234, USA, USA-Product

User, 9999, UK, UK-Marketing

];

concatenate(excel)

database:

load * inline [

TYPE, ID, LOCATION, PK

User, 3333, MX, MX-Finance

User, 3333, MX, MX-Marketing

User, 3333, MX, MX-Product

User, 3333, CAN, CAN-Marketing

User, 3333, CAN, CAN-Product

User, 1234, USA, USA-Finance

]

where not exists(ID);

resulting table:

load * inline [

TYPE, ID, LOCATION, PK

User, 1234, USA, USA-Product

User, 9999, UK, UK-Marketing

User, 3333, MX, MX-Finance

];

What I want:

load * inline [

TYPE, ID, LOCATION, PK

User, 1234, USA, USA-Product

User, 9999, UK, UK-Marketing

User, 3333, MX, MX-Finance

User, 3333, MX, MX-Marketing

User, 3333, MX, MX-Product

User, 3333, CAN, CAN-Marketing

User, 3333, CAN, CAN-Product

];

Many thanks!!

[Oleg_Troyansky · Accepted solution]

Yes, this is a known behavior with not exist() - as soon as you load one row of data with a certain ID, the rest of them render the condition as false and aren't loaded.

In order to overcome this issue, you can load the same ID with another field name, like ExcelID for example, and enhance your condition by comparing the ID field value with existing values of ExcelID, like this:

WHERE NOT EXISTS(ExcelID, ID)

Cheers,

[MEBG93]

Great! it works!. What I get is:

excel:

load * inline [

TYPE, ExcelID, ID, LOCATION, PK

User, 1234, 1234,USA, USA-Product

User, 9999, 9999, UK, UK-Marketing

];

concatenate(excel)

database:

load * inline [

TYPE, ID, LOCATION, PK

User, 3333, MX, MX-Finance

User, 3333, MX, MX-Marketing

User, 3333, MX, MX-Product

User, 3333, CAN, CAN-Marketing

User, 3333, CAN, CAN-Product

User, 1234, USA, USA-Finance

]

where not exists(ExcelID, ID);

load * inline [

TYPE, ExcelID, ID, LOCATION, PK

User, 1234,  1234  , USA, USA-Product

User, 9999,   9999 ,UK, UK-Marketing

User,           ,3333, MX, MX-Finance

User,            ,3333, MX, MX-Marketing

User,            ,3333, MX, MX-Product

User,            ,3333, CAN, CAN-Marketing

User,            ,3333, CAN, CAN-Product

];