3 Replies Latest reply: Jul 3, 2013 11:51 AM by Bogdan Cristescu RSS

    Comparing values in the same table

    Bogdan Cristescu

      Hello,

       

      I'm struck and I need your advice.

       

       

      yearCustomerIDProductIDflag
      2012b001a001lost
      2012b002a002
      2012b003a003
      2012b004a004lost
      2013b002a002renew
      2013b005a001new
      2013b006a003new
      2013b003a003renew

       

      I have a table with year, customerID and productID and I want to create a column named flag (preferably in the load script). I would also be satisfied with creating 3 columns (lost,renew,new), but I don't know exactly how to do this with values from the same table.

       

      renew= customerID&productID exist in 2013 and also in 2012.

      lost = customerID&productID exist in 2012 but not in 2013

      new = customerID&productID exist in 2013 but not in 2012

       

      How can I compare the values? My guess would be to use one of the functions: exists, lookup or peek.

       

      Thank you in advance.