4 Replies Latest reply: Aug 17, 2018 5:11 AM by Gabor Tarnoczai RSS

    Reverse inner keep

    Vincent Bellanger

      Greetings everyone,

       

      I have 2 tables like those:

       

      Name

      School

      Franck DamicoBerclay
      Mark BrendanawiczHarvard
      Ramuh LeviathanCentrale
      Jake Peralta

      NY Excelsior

      School
      Berclay
      Harvard

       

      First table is my Principal table, the second one is my exclusion table.

       

      I want the data that are on my second table to be removed from the first one to have a result like this

       

      NameSchool
      Ramuh LeviathanCentrale
      Jake PeraltaNY Excelsior

       

      I was trying to use keep but i couldn't find a way to revert it to keep the data that are NOT in another table.

       

      Thanks for the answers!

        • Re: Reverse inner keep
          Gabor Tarnoczai

          Hi Vincent,

           

          you can't exclude the second table's values in one step.

          Please look into this piece of code:

           

          tmp:
          Load
               Name,
               School
          From/Resident [table];
          
          left join(tmp)
          Load
               School
               1 as delete_flg
          From/Resident [exclude table];
          
          Final:
          Load
               Name,
               School
          Resident tmp
          Where delete_flg=1;
          
          drop table tmp;
          
          

           

          So first step is join the exclude table to the base table, with a flag, and in the next step you can filter out the values based on the previously created flag.

           

          G.

          • Re: Reverse inner keep
            Ivan Bozov

            Try as follows:

             

            Table_1:
            LOAD
                 *
            INLINE [
            Name,School
            Franck Damico,Berclay
            Mark Brendanawicz,Harvard
            Ramuh Leviathan,Centrale
            Jake Peralta, NY Excelsior];
            
            
            Table_2:
            LEFT JOIN LOAD
                 School,
                 'Delete' AS Flag
            INLINE [
            School
            Berclay
            Harvard];
            
            
            Final:
            NOCONCATENATE LOAD
                 *
            RESIDENT Table_1
            WHERE Flag <> 'Delete';
            DROP TABLE Table_1;
            DROP FIELD Flag FROM Final;
            
            
            
            
            

             

            Capture.PNG

              • Re: Reverse inner keep
                Gabor Tarnoczai

                Yeah, I mistyped the where condition

                thank for inform me

                 

                tmp:
                Load
                    Name,
                    School
                From/Resident [table];
                
                left join(tmp)
                Load
                    School
                    1 as delete_flg
                From/Resident [exclude table];
                
                Final:
                Load
                    Name,
                    School
                Resident tmp
                Where delete_flg<>1;  
                
                drop table tmp;
                
                
                
                

                I corrected the Where delete <> 1; expression;


                G.

              • Re: Reverse inner keep
                Chennaiah Nallani

                try like below

                 

                Table2:

                LOAD

                    School

                FROM [lib://c/****.xlsx]

                (ooxml, embedded labels, table is Sheet4);

                 

                 

                Table1:

                LOAD

                    Name,

                    School

                FROM [lib://c/*****.xlsx]

                (ooxml, embedded labels, table is Sheet3)

                where not Exists(School);

                 

                 

                Drop table Table2