topic Find a value between a numeric range in QlikView

Find a value between a numeric range

sohailansari201 — Wed, 30 Jul 2014 16:42:41 GMT

In the screenshot, you can see the data model up top and the required straight table in the bottom.

There is a student named John, who has scores in different subjects. These scores are evaluated among different ranges and received certain points (I have highlighted the values for John), and then the sum of the points determines the grade. Please note that "GradingScheme" table is not linked to any other table in the data model (this is intentional)

I am able to figure out the Expression1 which is the sum of all available points for John (for e.g. sumPoints) for any student, but I am not able to figure out the Expression2 which is to calculate the grade.

I have tried some of these:

MaxString(if(sumPoints >= MinPoints AND sumPoints <= MaxPoints, Grade))

MaxString({<MinPoints={"<=(sumPoints)"},MaxPoints={">=$(sumPoints)"}>}Grade)

But it always gives out the - as the Expression2 value.

Please help.

Thank you.

Re: Find a value between a numeric range

Wed, 30 Jul 2014 17:25:24 GMT

Hi,

Have u tried it using interval match function?

Re: Find a value between a numeric range

sohailansari201 — Wed, 30 Jul 2014 17:38:15 GMT

Thanks Soumya, but I believe that Interval Match only works script load area. Whereas this needs to be done on the expression level in a straight table.

Thank you,

Re: Find a value between a numeric range

Wed, 30 Jul 2014 18:03:25 GMT

Hi,

Try a simple IF, something like this:

If(Sum(Points)>=75 and Sum(Points)<=100,

'A',

if(Sum(Points)>=50 and Sum(Points)<=74,

'B',

'C'

)

It's rustic but works.

Or try using a Class function but the problem with class is you need to specify a static increment for example 25 and you have segments 0-25, 26-50, 51-75, 76-100 ...

Best regards.

Re: Find a value between a numeric range

sohailansari201 — Wed, 30 Jul 2014 18:11:30 GMT

Thanks Oswaldo,

It works with this type of 'hardcoded' grades. The requirement is not to hardcode the grade in the if condition. Because the values will be changing in the GradingScheme table. It has to read the Grade from the GradingScheme table like for e.g.

If(Sum(Points)>=MinPoints and Sum(Points)<=MaxPoints, Grade)

Thanks again.

Re: Find a value between a numeric range

sohailansari201 — Wed, 30 Jul 2014 20:12:51 GMT

Anyone please?