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:

Customer	Rating_ID	Rating
A	001	1
A	004	3
A	006	4
B	002	3
B	005	2
C	003	1

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.

[swuehl · Accepted solution]

Try something like

LOAD Customer,

          FirstSortedValue( Rating, -Rating_ID) as LastRating

RESIDENT YourTable

GROUP BY Customer;

[richard_cioci]

Thanks!

I also came up with this one:

Ratings:

Load

Customer,

max(Rating_ID) as Rating_ID

Resident Table

Group by Customer;

Left Join (Ratings)

Load

Customer,

Rating_ID,

Rating

Resident Table;

Do you know which of the two methods (first sorted value or mine) would be more efficient with a large list of customers?