# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New 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:

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.

1 Solution

Accepted Solutions
Honored Contributor II

## Re: How to structure data

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

Learning never stops.
8 Replies
Honored Contributor II

## Re: How to structure data

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

Learning never stops.
New Contributor

## Re: How to structure data

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.

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

[map1]:

map1:

(ooxml, embedded labels, table is Data);

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

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

(ooxml, embedded labels, table is Data);

Honored Contributor II

## Re: How to structure data

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

Learning never stops.
New Contributor

## Re: How to structure data

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

Honored Contributor II

## Re: How to structure data

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

Learning never stops.
New Contributor

## Re: How to structure data

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.

Brayden

Honored Contributor II

## Re: How to structure data

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

Learning never stops.
New Contributor

## Re: How to structure data

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:

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?