topic ApplyMap() ? in QlikView

ApplyMap() ?

Mon, 06 Feb 2012 10:16:01 GMT

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.

Re: ApplyMap() ?

kaushiknsolanki — Mon, 06 Feb 2012 10:25:00 GMT

Hi,

Have a look at the application attached here with.

Regards,

Kaushik Solanki

ApplyMap() ?

Mon, 06 Feb 2012 10:37:45 GMT

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...

ApplyMap() ?

swuehl — Mon, 06 Feb 2012 10:53:49 GMT

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

Re: ApplyMap() ?

Mon, 06 Feb 2012 11:13:29 GMT

Hi,

please see the attached example (includs your data).

Good luck!

Rainer

ApplyMap() ?

Mon, 06 Feb 2012 11:56:35 GMT

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...

ApplyMap() ?

Mon, 06 Feb 2012 12:07:24 GMT

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

Hope you get the result you want !

Rainer