Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, good afternoon!
I have the following question, I have two tables "termination" and "ComplementaryTerminations" where the key among them is cdn_emresa, cdn_estab, cdn_funcionario, month and year. How can I load a third table that is the result of everything that is on Terminations + which has no counterpart in "ComplementaryTerminations"?
example
terminations
Emp Est Employ Month Year
1 1 1 10 2013
1 1 2 10 2013
1 1 4 10 2013
1 1 5 10 2013
ComplementaryTerminations
Emp Est Employ Month Year
1 1 1 10 2013
1 1 3 10 2013
1 1 4 10 2013
1 1 5 10 2013
FinalTable
Emp Est Func Month Year
1 1 1 10 2013
1 1 2 10 2013
1 1 3 10 2013
1 1 4 10 2013
1 1 5 10 2013
How can I load a third table that is the result of everything that is on Terminations + which has no counterpart in "ComplementaryTerminations"?
I would expect only
1 1 2 10 2013
to be in the FinalTable then. Or what do you mean with 'counterpart'?
If you want to check if a record in terminations table does not exist also in Complementary Terminations, try maybe
ComplementaryTerminations:
LOAD *,
AutoNumberHash128(Emp, Est, Employ, Month, Year) as Key1
INLINE [
Emp, Est, Employ, Month, Year
1, 1, 1, 10, 2013
1, 1, 3, 10, 2013
1, 1, 4, 10, 2013
1, 1, 5, 10, 2013
];
terminations:
LOAD *
INLINE [
Emp, Est, Employ, Month, Year
1, 1, 1, 10, 2013
1, 1, 2, 10, 2013
1, 1, 4, 10, 2013
1, 1, 5, 10, 2013
] where not exists( Key1, AutoNumberHash128(Emp,Est,Employ, Month, Year));
drop table ComplementaryTerminations;
How can I load a third table that is the result of everything that is on Terminations + which has no counterpart in "ComplementaryTerminations"?
I would expect only
1 1 2 10 2013
to be in the FinalTable then. Or what do you mean with 'counterpart'?
If you want to check if a record in terminations table does not exist also in Complementary Terminations, try maybe
ComplementaryTerminations:
LOAD *,
AutoNumberHash128(Emp, Est, Employ, Month, Year) as Key1
INLINE [
Emp, Est, Employ, Month, Year
1, 1, 1, 10, 2013
1, 1, 3, 10, 2013
1, 1, 4, 10, 2013
1, 1, 5, 10, 2013
];
terminations:
LOAD *
INLINE [
Emp, Est, Employ, Month, Year
1, 1, 1, 10, 2013
1, 1, 2, 10, 2013
1, 1, 4, 10, 2013
1, 1, 5, 10, 2013
] where not exists( Key1, AutoNumberHash128(Emp,Est,Employ, Month, Year));
drop table ComplementaryTerminations;