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!!
