2 Replies Latest reply: Feb 29, 2016 10:56 AM by Richard Cioci RSS

    Trying to create a script to only load the most recent rating for each customer.

    Richard Cioci

      I have a number of tables pulling in from a database.

       

      One of the tables is a customer rating, for the example of this post, let's say each customer has a rating from 1-4. Customer ratings can be updated, in which case the unique rating_id will be larger for the newer rating, but the older rating will be included.

       

      I.E.

      Customer List:

      A

      B

      C

       

      Rating Table:

      CustomerRating_IDRating
      A0011
      A0043
      A0064
      B0023
      B0052
      C0031

       

      In the example above, Customer A was rated a 1, then 3, then 4. Customer B was rated a 3, then a 2. Customer C was rated a 1.

       

      So I need the load script to output a 1 for A, a 2 for B and a 1 for C which I would then map or join to the existing customer table.

      How could I do this?

       

      I was considering a loop in the load script which would, for every customer, go through each row of that customer and set the rating_id to a variable (X) and set the rating as another variable (Y). Then it goes to the next record and compares the rating_id to the rating_id variable (X), and if it's smaller it does nothing, if it's larger, it sets the new rating_id as (X) and the rating for that record as (Y). At the end of the loop, it uses the (Y) variable to set the rating for a customer.

       

      However, before I figure out how to do this, I wanted to know if there's an easier and more efficient way given the number of customers in the table (it's large).

       

      I've attached a sample of the above table.