Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
"....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?
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).