map a fixed month salary to a variable salary

[segerchr]

Hey,

i want to joint to tables in one.

On the right side there are variable salary on specific dates. On the left side there are a fixum once a time in the month.

it is my goal to have a table like the CalMon in the example where the fixum is in a new column once for Nov and once for oct.

Thanks for helping.

BR

CS

[migueldelval]

Hi Chirstian,

Like this:

Regards

Miguel del Valle

[segerchr]

Thanks for your answer, but the problem is that i have two specific earns in october and one in november. in your solution the fix is in october not once. You have two fix in october. It should display one fix salary per month 🙂

The table should display the following for ID1 :

KalMonat - Date - Budget - Earn - Sum - Fix

Nov - 09.11.2016 - 50 - 11 - 61 - 50

Oct - 04.10.2016 - 20 - 10 - 30 - 50

Oct - 08.10.2016 - 30 - 20 - 50 - 0

The fix should only display one in october and one in november.

THANKS

[Aurelien_Martinez]

Hi,

fx:
load * inline
[ID, Fix
1,50
2,100
3,80
4,200];


//left join
toto:
load * inline
[KalMonat, Date, Earn, Budget
Oct, 04.10.2016, 10,20
Oct, 08.10.2016,20,30
Nov,09.11.2016,11,50 ]
;


Right Join (fx)
LOAD
  KalMonat,
  alt(If(Peek('KalMonat')=KalMonat,Peek('ID') + 1), 1) as ID,
  Date,
  Sum(Budget) as Budget,
  Sum(Earn) as Earn
Resident toto

Group by KalMonat, Date
;


DROP Tables toto;

Result :

Budget	Date	Earn	Fix	ID	KalMonat
20	04.10.2016	10	50	1	Oct
30	08.10.2016	20	100	2	Oct
50	09.11.2016	11	50	1	Nov

[segerchr]

Hi, thanks for helping.

The ID is the name of the employee. So the Table without the fix is the same for everyone.

KalMonat - Date - Budget - Earn - Sum

Nov - 09.11.2016 - 50 - 11 - 61

Oct - 04.10.2016 - 20 - 10 - 30

Oct - 08.10.2016 - 30 - 20 - 50

When i choose employee 1 (ID1) the table (or another table) should display the following data.

KalMonat - Date - Budget - Earn - Sum - fix

Nov - 09.11.2016 - 50 - 11 - 61 - 50

Oct - 04.10.2016 - 20 - 10 - 30 - 50

Oct - 08.10.2016 - 30 - 20 - 50 - 0

When i choose employee 2 (ID2) the table (or another table) should display the following data.

KalMonat - Date - Budget - Earn - Sum - fix

Nov - 09.11.2016 - 50 - 11 - 61 - 100

Oct - 04.10.2016 - 20 - 10 - 30 - 100

Oct - 08.10.2016 - 30 - 20 - 50 - 0

The fix is a monthly based fix salery for each month.

Thanks

[Aurelien_Martinez]

I think it's ok (but you can improve the code ^^)

fx:
load * inline
[ID, Fix
1,50
2,100
3,80
4,200];


//left join
toto:
load * inline
[KalMonat, Date, Earn, Budget
Oct, 04.10.2016, 10,20
Oct, 08.10.2016,20,30
Nov,09.11.2016,11,50 ]
;


titi:
Join (fx)
LOAD
  Min(Date) as Date,
  Min(Date) as Date_exist,
  KalMonat
Resident toto
Group By KalMonat
;


Left Join(fx)
LOAD
  *
Resident toto
;




Join(toto)
LOAD Distinct
  ID,
  0 as Fix
Resident fx
;


Concatenate(fx)
LOAD
  *
Resident toto
Where not Exists(Date_exist, Date)
;


DROP Table toto;