topic Linear Table in QlikView

Linear Table

Mon, 14 Nov 2011 16:00:23 GMT

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

Linear Table

vgutkovsky — Mon, 14 Nov 2011 18:10:03 GMT

The simplest way to do this is via sorting. So, if your chart is a straight table, go to Sort, select Score, change the sort to Numeric Value descending and promote it to the top. Then, in Presentation, select "Max Number" and set it to 5.

Regards,

Vlad

Linear Table

Tue, 15 Nov 2011 10:25:48 GMT

HI Vlad,

first of all thank you for your reply.

I have to tell you that I already tried that way. It's a good way to show only the best five results but if I try to sum all the rows (about score) I obtain a wrong result because it sums not only the five rows but all the rows.

Thank you so much

Re: Linear Table

vgutkovsky — Tue, 15 Nov 2011 15:21:53 GMT

For something like that, I think you would get the best performance out of using the rank function in set analysis. So, first create a field called "Full Name" in the script that is just this:

Name & ' ' & Surname as [Full Name]

Then you can either use this Full Name as your new table dimension or leave it as 2 dimensions in the chart, Name and Surname. Replace your expression with the following:

sum({<[Full Name]={"=rank(total sum(Score))<=5"}>} Score)

You don't need to set any max-visibility settings in Presentation anymore.

Regards,

Vlad

Linear Table

Wed, 16 Nov 2011 09:03:35 GMT

Hi Vlad,

I'm going to do it immediately.

I'll tell you later about it.

THANK YOU SO MUCH

Linear Table

Wed, 16 Nov 2011 09:53:06 GMT

Hi Vlad,

unfortunately it doesn't work.

I did the steps you suggest me.

The result is:

Full Name Score Expression

Paul Berry 823,14 823,14

Paul Berry 812,11 812,11

....

Expression = sum({<[Full Name]={"=rank(total sum(Score))<=5"}>} Score)

THANK YOU SO MUCH

Re: Linear Table

rohit214 — Wed, 16 Nov 2011 11:00:15 GMT

i attached example plz find it

Linear Table

Wed, 16 Nov 2011 11:23:26 GMT

Hi Rohit,

First of all THANK YOU.

Unfortunately, if I well understood the example, it doesn't help me.

You take the max(UnitPrice) of all customers. So you have the first 5 Customers with the max Price.

My problem is different. I'll show you with an example:

Full Name Score

Paul Barry 790,15

Paul Barry 801,13

Paul Barry 847,12

Paul Barry 615,77

Paul Barry 864,98

Paul Barry 802,56

Paul Barry 823,78

My goal is obtain a results like this:

Full Name Score

Paul Barry 864,98

Paul Barry 847,12

Paul Barry 823,78

Paul Barry 802,56

Paul Barry 801,13

total 4139,57

Full Name Score Total

Paul Barry 864,98 4139,57

Paul Barry 847,12

Paul Barry 823,78

Paul Barry 802,56

Paul Barry 801,13

I hope it helps to understand my problem

THANK YOU SO MUCH

Re: Linear Table

vgutkovsky — Wed, 16 Nov 2011 15:33:13 GMT

Please clarify, what is the dimension of your chart? If it's just Name, then why would you expect to see the same name 5 times?

Linear Table

Wed, 16 Nov 2011 16:17:52 GMT

I'll try to clarify to you my problem.

The dimensions in my chart (table) are:

FullName, Dinstance, Stroke, Time, Score

So I have my table built in this way

FullName, Dinstance, Stroke, Time, Score

Paul Berry 50 FreeStyle 22"30 823,78

Paul Berry 50 BackStroke 26"30 803,78

Paul Berry 100 Breaststroke 57"30 893,78

Paul Berry 50 Butterfly 25"30 623,78

Paul Berry 100 Individual Medley 59"30 798,78

Paul Berry 200 Free Style 1'43"30 855,78

Paul Berry 50 Breaststroke 30"30 878,78

Paul Berry 50 FreeStyle 23"10 810,78

What I'm expecting to have are the best five results based on the Score.

I can't have more than one row with the same dinstance and stroke per Athlet.

So, if I have ( 50 FreeStyle 22"30 823,78) and (50 FreeStyle 23"10 810,78) I have to take (50 FreeStyle 30"3022"30 823,78).

About this I built this expression (with the label "BestFive") :

Aggr(Max(Score),Dinstance,Stroke)

it works perfectly.

So my results up to now is:

FullName, Dinstance, Stroke, Time, BestFive

Paul Berry 50 FreeStyle 22"30 823,78

Paul Berry 50 BackStroke 26"30 803,78

Paul Berry 100 Breaststroke 57"30 893,78

Paul Berry 50 Butterfly 25"30 623,78

Paul Berry 100 Individual Medley 59"30 798,78

Paul Berry 200 Free Style 1'43"30 855,78

Paul Berry 50 Breaststroke 30"30 878,78

Paul Berry 50 FreeStyle 23"10 -

What I would like to have is:

FullName, Dinstance, Stroke, Time, BestFive

Paul Berry 100 Breaststroke 57"30 893,78

Paul Berry 50 Breaststroke 30"30 878,78

Paul Berry 200 Free Style 1'43"30 855,78

Paul Berry 50 FreeStyle 22"30 823,78

Paul Berry 50 BackStroke 26"30 803,78

Total 4300,00

I'm sorry it isn't easy to me to explain it. I hope it's clear.

Again THANK YOU SO MUCH for you help.

Best regards,

Giampiero

Re: Linear Table

vgutkovsky — Wed, 16 Nov 2011 16:33:56 GMT

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)?

Linear Table

Thu, 17 Nov 2011 08:17:43 GMT

Hi Vlad,

I want to see the top 5 for each athlete.

Thanks

Regards,

Giampiero

Re: Linear Table

vgutkovsky — Thu, 17 Nov 2011 14:58:05 GMT

See attached for one possible solution.

-Vlad

Linear Table

Thu, 17 Nov 2011 15:20:05 GMT

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

Re: Linear Table

vgutkovsky — Thu, 17 Nov 2011 17:27:06 GMT

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

Linear Table

Fri, 18 Nov 2011 09:18:26 GMT

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

Linear Table

vgutkovsky — Sat, 19 Nov 2011 07:31:08 GMT

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

Linear Table

Mon, 21 Nov 2011 08:38:05 GMT

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

Linear Table

vgutkovsky — Mon, 28 Nov 2011 22:32:46 GMT

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

Re: Linear Table

Tue, 29 Nov 2011 09:25:04 GMT

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