2 Replies Latest reply: Jun 21, 2018 4:30 PM by Todd Buss RSS

    Convert sequence number into ordinal value during load

    Todd Buss

      I had this question, and then my agency's ETL guys already solved the problem.  Now my question is purely academic....


      Here's a fictional table to illustrate my question:

       

      Owner, pet, sequence

      bill, cat, 1

      bill, dog, 2

      bill, fish, 3

      jack, cat, 3  //note there is no sequence 1&2 for jack.

      jill, dog, 1

      jill, cat, 4 //note there is no sequence 2&3 for jill.

       

      during the data load, i need to assign a value to each record that indicates the ordinal value of each pet.  I want to add a field to the table called "order":


      Owner, pet, sequence, order

      bill, cat, 1,1

      bill, dog, 2,2

      bill, fish, 3,3

      jack, cat, 3,1  //note there is no sequence 1&2 for jack.  the cat is the first pet in the order.

      jill, dog, 1,1

      jill, cat, 4,2   //note there is no sequence 2&3 for jill. the cat is the 2nd pet in the order.