2 Replies Latest reply: Jan 15, 2018 1:04 PM by John Hillock RSS

    Where Exists Question

    John Hillock

      Hi all, I have the following data table which I'm trying to consolidate using Where Not Exists. Looked around and couldn't find something to fix this so if anyone has suggestions, please let me know. Thanks.

       

      MgmtBase:
      LOAD * INLINE [
          idCC, User, Type
          1, JOHNDOE, OWNER
          2, JOHNDOE, OWNER
          3, JOHNDOE, OWNER
          1, JANEDOE, BACKUP
          2, JANEDOE, BACKUP
          3, JANEDOE, BACKUP
          4, JANEDOE, BACKUP
          5, JANEDOE, BACKUP
      ];
      
      MgmtMap:
      NoConcatenate
      LOAD idCC,
      User
      Resident MgmtBase
      Where Type = 'OWNER';
      
      Concatenate(MgmtMap)
      LOAD idCC,
      User
      Resident MgmtBase
      Where Type = 'BACKUP' AND Not Exists (idCC);
      
      DROP Table MgmtBase;
      
      

       

      I'm expecting an output:

      idCCUser
      1JOHNDOE
      2JOHNDOE
      3JOHNDOE
      4JANEDOE
      5JANEDOE

       

      But I'm actually getting the following:

      idCCUser
      1JOHNDOE
      2JOHNDOE
      3JOHNDOE

       

      For some reason I can't get it to work. Any ideas why this is happening?

        • Re: Where Exists Question
          Sunny Talwar

          Try this

           

          MgmtBase: 

          LOAD * INLINE [ 

              idCC, User, Type 

              1, JOHNDOE, OWNER 

              2, JOHNDOE, OWNER 

              3, JOHNDOE, OWNER 

              1, JANEDOE, BACKUP 

              2, JANEDOE, BACKUP 

              3, JANEDOE, BACKUP 

              4, JANEDOE, BACKUP 

              5, JANEDOE, BACKUP 

          ]; 

           

          MgmtMap: 

          NoConcatenate 

          LOAD idCC as idCCTemp, 

          User, 

          Type 

          Resident MgmtBase 

          Where Type = 'OWNER'; 

           

          Concatenate(MgmtMap) 

          LOAD idCC as idCCTemp, 

          User, 

          Type 

          Resident MgmtBase 

          Where Type = 'BACKUP' AND Not Exists (idCCTemp, idCC); 


          DROP Table MgmtBase;

          RENAME Field idCCTemp to idCC;