Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bashdown
Contributor
Contributor

How to structure data

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
5.77
5.88
5.99
5.10a10.2
5.10b10.4
5.10c10.6
5.10d10.8
5.11a11.2

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!

1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

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.

regards

Pradosh

Learning never stops.

View solution in original post

8 Replies
pradosh_thakur
Master II
Master II

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.

regards

Pradosh

Learning never stops.
bashdown
Contributor
Contributor
Author

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:

[map1]:

// Load mapping table of grades:

map1:

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

(ooxml, embedded labels, table is Data);

[finalgrade]:

  Load *,

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

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

(ooxml, embedded labels, table is Data);

pradosh_thakur
Master II
Master II

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.

regards

Pradosh

Learning never stops.
bashdown
Contributor
Contributor
Author

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

Regards,

Brayden

pradosh_thakur
Master II
Master II

hi

then apply map is not at all needed

create an expression with if statement

if diff_value >=7 and <= 8 then final grade is 5.7

and so on  and name it as final_grade

let me know if it made sense.

regards

Pradosh

Learning never stops.
bashdown
Contributor
Contributor
Author

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.

Brayden

pradosh_thakur
Master II
Master II

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.

regards

Pradosh

Learning never stops.
bashdown
Contributor
Contributor
Author

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?