1 Reply Latest reply: Oct 9, 2013 3:48 PM by Stefan Wühl RSS

    Differences between tables.

    hugo cabrak

      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

        • Re: Differences between tables.
          Stefan Wühl

          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;