27 Replies Latest reply: Dec 1, 2011 10:20 AM by Giampiero Cina

# Linear Table

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

• ###### Linear Table

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

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

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

Hi Vlad,

I'm going to do it immediately.

I'll tell you later about it.

THANK YOU SO MUCH

• ###### Linear Table

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

hi

i attached example plz find it

• ###### Linear Table

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

OR

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

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

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

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

Hi Vlad,

I want to see the top 5 for each athlete.

Thanks

Regards,

Giampiero

• ###### Re: Linear Table

See attached for one possible solution.

-Vlad

• ###### Linear Table

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

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

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

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

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

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

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

• ###### Re: Linear Table

It's too complicated to do in the UI so I had to take the logic to the script. See attached.

Regards,

Vlad

• ###### Linear Table

Hi Vlad,

thank you so much.

It works perfectly.

I'm glad to know it's possible to solve my problem only using the logic script because I was thinking i didn't understand at all the rank function

Again, thank you so much for your kindless.

Best regards,

Giampiero

• ###### Linear Table

I was thinking a solution using variables in the UI.

I have a question for you Vlad.

I can use global variables in the UI ? If I well undesrtood variables in QLIK are only locals.

For instance, If I use a variable VScore in my table, and I build an expression like this: VScore = Score, it changes valure for each rows of my table and I can use it in a global way (for instance in an other table).

I hope I was clear

regards,

Giampiero

• ###### Re: Linear Table

I don't think that will work, or at least I don't see any way you can do this with variables or the rank function. The problem with RANK is that it only allows qualifier "total" across all dimensions, so for example, "rank(total <FullName> Score)" won't work.

If you want to play around with variables, you can, though. If you precede a variable definition with a "=" then it will be evaluated once per application. If there is no "=" then you can use it to store expressions. So, for example, variable:

=max(Score) will return the maximum total score across all values

max(Score) can be used with variable expansion to replace an expression that will respect chart dimensions.

Regards,

Vlad

• ###### Linear Table

I see.

Well i was trying to improve my knowledge on Qlik

I think that lots of problems I will face on Qlik I have to solve it using a mix between logic script and UI.

I learned a lot about Qlik thanks to you Vlad.

Thanks a lot

Regards,

Giampiero

• ###### Linear Table

Hi,

I'm a bit obstinate and I keep trying some experiment with variables.

I created a variable Vtest, then in my table I created two expressions:

1) If(Score='821.78',vTest=10,vTest=20)

2) if(Score='818.78',vTest = vTest * 10,vTest = vTest * 20)

In this way I'd wanted to assigne in vTest variable the values 10 or 20 and then modify vTest in 100 or 200 ... but it doesn't work.

What I'm trying to do is to use variables in the UI to make some calculation.

Regards,

Giampiero

• ###### Re: Linear Table

Giampiero,

You can't assign a variable value from a chart, you can only read it in. I'm not exactly sure why you need variables in your application at all...

Regards,

Vlad

• ###### Linear Table

Hi Vlad,

I was just trying to use variables in a different way

After my attempt, I understood it's not possible to assign a value to a variable in my chart.

You confirm to me it's not possible.

Thank you so much

Best regards,

Giampiero