8 Replies Latest reply: Oct 5, 2017 11:04 AM by brayden ashdown RSS

    How to structure data

    brayden ashdown

      Hi all,


      I'm trying to build a visualization to track my progress in rock climbing.  Rock climbing grades go from 5.0 up to 5.15 with smaller increments (5.10a, 5.10b, 5.10c, 5.10d - does not go beyond d) typically after 5.10.  I would like to be able to find the average grade at which I am climbing any given month (I have a few years of data), but a simple average obviously sees 5.9 as significantly larger than 5.10 or 5.11.


      I've created a table assigning an arbitrary value to each number as below:

      GradeDiff Value


      This allows me to find the average difficulty value, but I would like my visualization to report back the text string assigned to the value (5.10a instead of 10.2).


      Two questions:

           1. How do I get qlik to report the "Grade" instead of the "Difficulty Value"?  (similar to a vlookup in excel? mapping?)

           2. When I average out all of the climbs, it will obviously not return a value that directly correlates to my table (10.51435 for example) - what is the best way to deal with rounding or looking for the closest match in the "Difficulty Value" field?

      Edit: Figured out the round function and have the averages reported in the correct format.


      Thanks in advance for your help!

        • Re: How to structure data
          pradosh thakur

          1:why not use applymap function in the script  and load the table


          2:if value are unique then use them directly in the expression (where required) Qlik will auto associate the data.





            • Re: How to structure data
              brayden ashdown

              Since originally posting I've spent a lot of time trying to get applymap to work the way I need it to and have not yet been able.   The main data source I am using is from one table which houses the [Diff Value] and the [Grade]. 


              • [Diff Value] is the numerical value that can be used to make an accurate average.


              • [Grade] is a text string that puts the numerical value back into the proper format for output.


              Ultimately, I want to take all of the raw data with hundreds of climbs, find the average [Data.Diff Value] for each month, but have the output be in terms of [Data.Grade].  I've been able to make a pivot table to get through the first part but not get the correct output. 


              • Dimension = Data.autocalendar.YearMonth
              • Measure = Round(Avg([Data.Diff Value]), 0.2)


              This gets me to the place where I have the average [Diff Value] for each month, but not I don't know how to get it to report those results as [Grade]. 


              When I tried my new applymap value [finalgrade] to the above pivot table it will not output any values.

              Screen Shot 2017-10-03 at 9.23.49 AM.png

              Here is the code I was using when trying apply map:



              // Load mapping table of grades:


              Mapping Load [Diff Value], [Grade] From [lib://AttachedFiles/Climbing Test.xlsx]

              (ooxml, embedded labels, table is Data);



                Load *,

                        ApplyMap('map1', [Diff Value], null()) as [finalgrade]

                        From  [lib://AttachedFiles/Climbing Test.xlsx]

              (ooxml, embedded labels, table is Data);

                • Re: How to structure data
                  pradosh thakur

                  as you don't have 9.6 or 9.2 as any grade ,apply map is not working here. my question is if it is 9.2 what grade you want to put ? next closest that is 9 and final grade should be 5.9 ? or is there any other business requirement ?


                  we can do that using some function and expression.




              • Re: How to structure data
                brayden ashdown

                Hi Pradosh,


                Thanks for your help, I didn't realize that if one of the values didn't fit the applymap, none would appear.  I figured I should at least get a value for 11.2.


                Ideally, I would have  (9.0-9.8 = 9), (8.0-8.8 = 8), (7.0-7.8 = 7). 





                • Re: How to structure data
                  brayden ashdown

                  Hi Pradosh,


                  I think I have finally arrived at the correct question to ask – thanks for your patience.  From my understanding, both applymap and the "if statement" serve as a static way to get the [Diff Value] mapped to the appropriate text string.  I went back and adjusted my source table to include values for 9.2 and 9.6 etc so that I now have all of that mapped appropriately. 


                  The real problem is that when I go to make my table, I have to use [Date.autocalendar.YearMonth] as the dimension, and a calculated field, =Round(Avg([Diff Value]),0.2), as my first measure.  When I use my mapped field [FinalGrade] as a second measure, it is applying it to the dimension [Date.autocalendar.YearMonth] and so I get a bad result. 


                  I think the answer is that I need some sort of calculated dimension.  I need a dimension that will give me the average [Diff Value] based on either [Date.autocalendar.YearMonth] or simply [Date].  Then if I apply my [FinalGrade] map as a measure I think it will work.


                  Thanks again for your help.



                    • Re: How to structure data
                      pradosh thakur

                      hi Brayden


                      i guess i too was not that clear.


                      you have added one dimension, and a calculated field as measure. now add another calculated field with if statement and give the conditions if it lies between this number than this grade and so on . hopefully it will work for you.

                      dim1= yearMonth


                      measure1= avg


                      measure 2= if (avg <8 and avg>=7 then grade = 5.7) use nested if to add all the condition.




                        • Re: How to structure data
                          brayden ashdown

                          Thanks Pradosh - that worked perfectly, though it does seem a little redundant to have a chart already mapping values to text strings and only have that available through the script and not expressions in the active app. 


                          This solved my biggest problem, but now I wonder (if you have the patience) if you know of a solution to trying to graph this data. 


                          Ideally I want a chart like this:

                          Screen Shot 2017-10-05 at 8.55.34 AM.png

                          where dimension = date.yearmonth

                          measure 1 = number of routes

                          measure 2 = FinalGrade (I saved the long list of nested "if statements" from my table)


                          Only problem is that, obviously, we just worked to make all of these values text strings instead of numbers, but the graph won't accept text strings for the y-axis (secondary axis). 


                          I thought I could get around it by using the dual() function in my nested if statements, and it does indeed get the correct line plotted, but when I hover over the data point, it's label is the number and not the text string.  Any way around this?  Here is a sample of the code I was using:


                          if(avg([Diff Value])<11.8 and avg([Diff Value])>=11.6,dual('5.11c',11.6),

                               if(avg([Diff Value])<12 and avg([Diff Value])>=11.8,dual('5.11d',11.8),


                          **I would like to have the graph utilize the number value for plotting the line (11.8), and the label to use the text string (5.11d).**


                          If that use is confusing, maybe a simpler use case would be a KPI with a dual() measure, displaying the text instead of the number – is this possible?