Differences between tables.

[#1]

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

[swuehl · Accepted solution]

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;