topic Re: Only return distinct rating per user for an article in QlikView

Only return distinct rating per user for an article

Thu, 15 Nov 2012 03:06:49 GMT

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!

Re: Only return distinct rating per user for an article

Thu, 15 Nov 2012 05:44:53 GMT

Hi beanz,

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

Regards,

Kiran Rokkam.

Re: Only return distinct rating per user for an article

jagan — Thu, 15 Nov 2012 07:44:22 GMT

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.

Re: Only return distinct rating per user for an article

Thu, 15 Nov 2012 08:20:32 GMT

Thanks both for your replies

Re: Only return distinct rating per user for an article

Sun, 18 Nov 2012 04:04:27 GMT

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.

Re: Only return distinct rating per user for an article

Sun, 18 Nov 2012 04:08:54 GMT

I seem to have fixed it.

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

Re: Only return distinct rating per user for an article

jagan — Mon, 19 Nov 2012 06:09:23 GMT

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.