ApplyMap() ?

[#1]

Hi all,

i have the following situation:

Two tables:

users:

User_ID	Quarter	Hours	Bonus_ID
123	Q1-2011	122	1
224	Q1-2011	103	1
234	Q2-2011	97	2

bonus:

Bonus_ID	From_Hours	To_Hours	Bonus
1	75	100	75%
1	101	125	100%
2	79	104	75%
2	105	130	100%

What i want (in my LOAD script):

I want to determine the correct bonus for each user based on the user's hours. I assume i have to check in which interval the users.Hours value falls when looing at the bonus table...but i am not sure how to do this - Can anyone help out or give me a hint ?

Thanks a lot.

K

[kaushiknsolanki]

Hi,

     Have a look at the application attached here with.

Regards,

Kaushik Solanki

[#3]

Thanks a lot for the prompt feedback.

I see only one problem: I have more than one bonus scheme (Bonus_ID); if i e.g. select user 123 i get to possible records in the interval table (for the same hours but one for each Bonus_ID), but it should be only one record / one bos as the user 123 is linked to Bonus_ID 1...

[swuehl]

Use the extended syntax for interval match, like

BONUS:

LOAD From_Hours,

     To_Hours,

     Bonus_ID, 

     Bonus

FROM

[http://community.qlik.com/thread/46676?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

USERS:

LOAD User_ID,

     Quarter,

     Hours,

     Bonus_ID

FROM

[http://community.qlik.com/thread/46676?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

join Intervalmatch (Hours, Bonus_ID)

LOAD From_Hours, To_Hours, Bonus_ID Resident BONUS;

left join load * resident BONUS;

drop table BONUS;

drop fields From_Hours, To_Hours,

Hope this helps,

Stefan

[#5]

Hi,

please see the attached example (includs your data).

Good luck!

Rainer

[#6]

Thanks a lot!

This seems only to work though with the current set of data. If i add a line to the xls sheet - e.g. a record for user 123 and Q2-2011 having worked 99 hours (Bonus_ID = 1), i get 2 results (75 & 100) for 99hours for user 123 in Q2 2011...

[#7 · Accepted solution]

Please eleminate the DROP FIELD statement in script (comment it out).

Hope you get the result you want !

Rainer