topic Re: help in script in QlikView

help in script

ali_hijazi — Wed, 12 Sep 2012 07:12:09 GMT

Dear

I got a table that contains foreach employee the date and the amount that he got as employee package

the table is as follows:

EmpId, Date, Amount

1, 30/6/2012, 1500

1, 30/7/2012, 1550

1, 31/8/2012, 1550

....

2, 30/6/2012, 2200

....

Another table contains each employee and the date he got bonus

as follows:

EmpId, BonusDate, Bonus

1, 2/5/2012 , 200

1, 15/7/2012, 150

2, 1/4/2012, 125

2, 1/9/2012, 175

....

what I want is the following to have a table that contains the employee, the date of package and the amount along with the last bonus less than or equal to the package date

i.e.

EmpId, Date, Amount , LastBonusDate, LastBonus

1, 30/6/2012, 1500, 2/5/2012, 200

1, 30/7/2012, 1550,15/7/2012, 150

1, 31/8/2012, 1550,15/7/2012, 150

....

2, 30/6/2012, 2200, 1/4/2012,125

....

Please advise

Thank you

Re: help in script

flipside — Wed, 12 Sep 2012 09:29:44 GMT

I think this works ...

Packages:
LOAD
*,
num(EmpId,'10000') & num(Date,'00000') as KeyComp
INLINE [
EmpId, Date, Amount
1,     30/06/2012, 1500
1,     30/07/2012, 1550
1,     31/08/2012, 1550
2,     30/06/2012, 2200
2,     29/07/2012, 2101
2,     20/08/2012, 1323
2,     30/09/2012, 3123];

Bonuses:
LOAD
*,
num(BonusEmpId,'10000') & num(BonusDate,'00000') as BonusKeyComp
INLINE [
BonusEmpId, BonusDate, BonusAmount
1,    02/05/2012, 200
1,    15/07/2012, 150
1,    31/08/2012, 950
2,    01/04/2012, 125
2,    01/09/2012, 175];

ComboTbl:
LOAD
RowNo() as ComboTblRef,
EmpId as ComboEmpId,
KeyComp as ComboKey
resident Packages;

left join (ComboTbl)
LOAD
BonusEmpId as ComboEmpId,
BonusKeyComp as ComboBonusKey
resident Bonuses;

left join (ComboTbl)
LOAD
ComboTblRef,
max(ComboBonusKey) as ComboLink
resident ComboTbl
where ComboKey - ComboBonusKey >=0
group by ComboTblRef;

left join (Packages)
LOAD
ComboKey as KeyComp,
max(ComboLink) as BonusKeyComp
resident ComboTbl
group by ComboKey;

drop table ComboTbl;

flipside

Re: help in script

ali_hijazi — Wed, 12 Sep 2012 10:56:08 GMT

nop

Re: help in script

flipside — Wed, 12 Sep 2012 11:18:59 GMT

The screenshot below is the table objects created after running my code, is this not what you required?

Re: help in script

ali_hijazi — Wed, 12 Sep 2012 13:15:54 GMT

Sorry it's yes