Only return distinct rating per user for an article

[#1]

Hi all,

Not sure if anyone has any ideas on this one.

I simply have, a user, an article they can view, a rating value that they rate a page and the time that the page is viewed/rated.

But sometimes,  for example : 1 user may rate an article with a value of 5 stars, but then change his mind and rate it as 4.

Now, I need to only record the last recorded rating. ie. 4 in this case.

How can I achieve this in a table? I probably need to distinct it somehow, or maybe it can be done with set analysis.

Any ideas?

I've attached a basic .qvw of the idea.

Many thanks!

[#2]

Hi beanz,

May be this is what you are looking for? PS: I modified data a bit.

Regards,

Kiran Rokkam.

[jagan]

Hi,

PFA file hope it helps you.  Calculating the latest and assigning a flag to the record will have better performance instead of calculating runtime.

Regards,

jagan.

[#4]

Thanks both for your replies

[#5]

Hi Jagan,

Seems to be on the right track, but I added a bit more dummy data to test, but it doesn't seem to be working out the latestRating value correctly.

If you look at the attached .qvw,

eg. User 'cc', should get a latestRating of 1 for both page1 and page2, but it seems to only be giving a value of 1 to page2

Both page1 and page2 should get a latestRating of 1, as it is a different page and a different time for both,

Do you maybe have any ideas on how to modify the If(Previous(user) <> user, 1, 0) AS latestRating expression, which calculates this.

Thanks for all your help.

[#6]

I seem to have fixed it.

Just changed the order by : ORDER BY article, user, datetime  DESC;

[jagan · Accepted solution]

Hi,

You need to order the date by descending then it works as you expect.  Please close this post you are done.

check my script

Temp:

LOAD * INLINE [

    datetime, user, rating, article

    2012/06/04 09:54, aa, 5, page1

    2012/06/04 10:00, aa, 5, page1

    2012/02/09 11:00, bb, 4, page2

    2012/02/09 11:20, bb, 3, page2

    2012/11/14 12:03, cc, 4, page1

    2012/11/14 12:06, cc, 5, page2

];

Data:

LOAD

    *,

    If(Previous(user) <> user, 1, 0) AS latestRating

RESIDENT Temp

ORDER BY user, datetime  DESC;

Hope this helps you.

Regards,

Jagan.