Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Lookup from a table with range

HI All

I've a table where I'm using a measure to calculate the average and if conditions like:

if(Ind = 'L',Sum(Sales))/Sum(minutes)  and this is computed across a table for each employee and his dept.

Based on this value, I need to provide an incentive, hence I've a range of incentives like

Range MinRange MaxValue
01100
1101301.2
1301501.4
1501000000001.8

 

If the computed value falls in the given range, I need to add a measure to the table stating the incentive the employee would get.

Thanks in advance!


Regards

Pratyush

Labels (1)
9 Replies
Highlighted
Specialist II
Specialist II

I think the IntervalMatch function can help.

Highlighted
Specialist
Specialist

The measure is computed at the visualization level, I don't want to do it in the load script.


Regards

Pratyush

Highlighted
Specialist
Specialist

XYZ.png

 Please findd the attached app. I've selected KGI and CFS as e-code and Week 30/2020.

Highlighted
Specialist
Specialist

Can anyone please look into this and help. I'm willing to clarify if there are any doubts or concerns.


Regards

Pratyush

Highlighted
Creator II
Creator II

Try something like this. 

Add the following as inline table in your load to work as the table to look up value:

load * Inline [
category, range, score
a, 150, 1.8
b, 130, 1.4
c, 110,1.2
d, 0, 0
] ;

Then you will need to add the following as a measure in your table:

IF(YOURAGGREGATEEXPRESSION >=
Only({1<category={'a'}>}range),
Only({1<category={'a'}>}score),
IF(YOURAGGREGATEEXPRESSION >=
Only({1<category={'b'}>}range),
Only({1<category={'b'}>}score),
If(YOURAGGREGATEEXPRESSION >=
Only({1<category={'c'}>}range),
Only({1<category={'c'}>}score),
IF(YOURAGGREGATEEXPRESSION >=
Only({1<category={'d'}>}range),
Only({1<category={'d'}>}score)
))))

Highlighted
Specialist
Specialist

What if the customer increases 1000s of range tomorrow. This solution would not work since it is not dynamic.

Highlighted
Specialist
Specialist

Hello everyone

I would really appreciate if someone can help on this. I've tried almost everything and really need your expertise here

@sunny_talwar  I know you can solve this 🙂

Regards

Pratyush

Highlighted
Specialist
Specialist

Please help. My customers need this today 😞

Highlighted

May be something like this

Aggr(
	If(Dept = [dept-desc] and (Sum({<[pk-ind] = {'I'}>}[o-sentqty])/(Sum(Minutes)/60)) >= Only({<Task={"Cases Assembled"}>}[Range Min]) and (Sum({<[pk-ind] = {'I'}>}[o-sentqty])/(Sum(Minutes)/60)) <= Only({<Task={"Cases Assembled"}>}[Range Max]), Only({<Task={"Cases Assembled"}>} Value))
, Dept, [Range Min], [Range Max], [e-code], [e-name], [pk-ind], [Assemblydate.autoCalendar.Week], [dept-desc])

The example you gave was not workable because the dept-desc was Grocery on them and I didn't see any Dept Grocery. There were only Frozen and Non-Frozen. I picked another example to test the data

image.png