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

    Linear Table

    Giampiero Cina

      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
          Vlad Gutkovsky

          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
              Giampiero Cina

              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
                  Vlad Gutkovsky

                  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

              • Re: Linear Table
                rohit gupta

                hi

                i attached example plz find it

                  • Linear Table
                    Giampiero Cina

                    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
                        Vlad Gutkovsky

                        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
                            Giampiero Cina

                            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
                                Vlad Gutkovsky

                                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
                                    Giampiero Cina

                                    Hi Vlad,

                                    I want to see the top 5 for each athlete.

                                    Thanks

                                    Regards,

                                    Giampiero

                                      • Re: Linear Table
                                        Vlad Gutkovsky

                                        See attached for one possible solution.

                                         

                                        -Vlad

                                          • Linear Table
                                            Giampiero Cina

                                            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
                                                Vlad Gutkovsky

                                                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
                                                    Giampiero Cina

                                                    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
                                                        Vlad Gutkovsky

                                                        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
                                                            Giampiero Cina

                                                            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
                                                                Vlad Gutkovsky

                                                                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
                                                                    Giampiero Cina

                                                                    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
                                                                        Vlad Gutkovsky

                                                                        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
                                                                            Giampiero Cina

                                                                            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
                                                                                Giampiero Cina

                                                                                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
                                                                                    Vlad Gutkovsky

                                                                                    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
                                                                                        Giampiero Cina

                                                                                        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
                                                                                            Giampiero Cina

                                                                                            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