Retrieve value that falls within the range of another table

[#1]

Hello everybody,

I have two tables, one contains the sales for an employee for a given year and month, the other one contains the bonus an employee receives if his/her sales for a whole year is larger than a certain cutoff value:

Sales:

LOAD * INLINE [

    year, month, employeeID, sales

    2013, 1, abc, 250

    2013, 2, abc, 350

    2013, 3, abc, 100

    2013, 1, def, 50

    2013, 2, def, 150

    2013, 3, def, 200

];

Bonus:

LOAD * INLINE [

    id, year, cutoff, bonus

    2013, 250, 100

    2013, 450, 200

    2013, 650, 300

    2013, 850, 400

];

What I would like to accomplish in the frontend (not in the script) is to create a list that returns the bonus for each employee depending on how the sales matches the cutoff ranges in the bonus table. Any idea how to achieve this?  I would appreciate any help!

Thanks a lot,

Christian

[tresesco]

You script should possibly be like:

Sales:

LOAD * INLINE [

    year, month, employeeID, sales

    2013, 1, abc, 250

    2013, 2, abc, 350

    2013, 3, abc, 100

    2013, 1, def, 50

    2013, 2, def, 150

    2013, 3, def, 200

];

Bonus:

Load

          id as employeeID,

          cutoff,

          bonus;

LOAD * INLINE [                                         // you missed populating id field

    id, year, cutoff, bonus

    2013, 250, 100

    2013, 450, 200

    2013, 650, 300

    2013, 850, 400

];

Now two tables would be associated with employeeID field. You can right front-end expression according to your need.

[#3]

Thanks treseco for your quick reply. How would I now do the kind of lookup that I described in the frontend? From what I understand there are functions such as intervalmatch that would accomplish this in the script, however I have not found a function for the frontend.

[tresesco]

"....create a list that returns the bonus for each employee depending on how the sales matches the cutoff ranges in the bonus table"

--- is not very clear to me. Could you explain bit more with expected output sample?

[#5]

employeeID     sales     bonus

abc                   700        300

def                    400        100

Employee abc generated a total sales of 700 and therefore receives a bonus of 300 (as indicated in the bonus table), employee def generated a total sales of 400 and receives a bonus of 100 (as indicated in the bonus table).