Linear Table

[#1]

Hi everyone,

I'm new on QLIK so I have some problem.

I've a question for you.

I did a Linear Table with 3 fields (Name,Surname,Score). I've 30 results (lines or record).

I'd like to Sum only the first 5 score and put it in a new column called "Best 5 scores".

How can I do an expression that sum only the first 5 records ??

Thank you so much

JP

[vgutkovsky]

Do you want to see the top 5 overall (across all athletes) or the top 5 for each althlete (so if you have 5 athletes, you would have 25 rows)?

[#3]

Hi Vlad,

I want to see the top 5 for each athlete.

Thanks

Regards,

Giampiero

[vgutkovsky · Accepted solution]

See attached for one possible solution.

-Vlad

[#5]

Hi Vlad,

THANK YOU, THANK YOU SO MUCH.

It works. I'm still a novice of QLIK so your way to develop on it it's still a bit high for me, but I learned a lot thanks to you.

Now I have only to understand how don't show in the top 5 a duplicate (example two 50 FreeStyle for Paul Berry) but I'm sure I can solve this problem developing the aggr function with distance and stroke.

Thank you so much for your help and kindness.

Best regards

Giampiero

[vgutkovsky]

No problem, glad I could help. If you want to show only 1 stroke/distance per athlete, I would do something like this:

if(sum(aggr(rank(sum(Score),4,1),FullName,Stroke,Distance,Score))=1 and sum(aggr(rank(sum(Score),4,1), FullName,Score)) <= 5,

sum(Score)

)

This will produce up to 5 results per athlete, but will only allow 1 result per stroke/distance/athlete.

Regards,

Vlad

[#7]

Again, Thank you.

Hope you don’t mind I take one more minute of your time.

The expression

if(sum(aggr(rank(sum(Score),4,1),FullName,Stroke,Distance,Score))=1 and sum(aggr(rank(sum(Score),4,1), FullName,Score)) <= 5,

sum(Score)

)

give me only 4 results per athlete.

I'm trying to understand why. Maybe the RANK instruction is not so clear to me.

Best regards

Giampiero

[vgutkovsky]

It will give you *up to* 5 results, but in this case you only have 4 combinations of distinct strokes/distance per athlete.

[#9]

HI Vlad,

I'm sorry I did a mistake explaining you the problem.

The expression give me five results, but in the five best score there are two 50 FreeStyle and this is not possible.

I'm trying to understand why 🙂

THANK YOU SO MUCH

[vgutkovsky]

Sorry for the delayed response, been on vacation. So you want the top 5 results per stroke/distance and not per stroke/distance/athlete?

[#11]

Hi Vlad,

glad to hear you again.

No problem at all and THANK YOU to be so kind.

I want the top 5 results per stroke/distance/athlete.

I attach you xls file to let you understand what I'm looking for.

I hope it will be useful.

Thanks a lot

Giampiero