Slicing into monthly records

[#1]

Good afternoon,

As input, I got HR data like

PersNr: 4711 Kevin Mueller

Date from: 15.03.2014

Date to: 20.12.2015

Cost Center: 2489 Strategy and Business Development

Salary: 80.000 EUR

I would like to transform this in the script into monthly slices, as this allows the end users to see time series based on month-end values. The data should look as follows

PersNr: 4711 Kevin Mueller

MonthEnd: 31.03.2014

Cost Center: 2489 Strategy and Business Development

Salary: 80.000 EUR

PersNr: 4711 Kevin Mueller

MonthEnd: 30.04.2014

Cost Center: 2489 Strategy and Business Development

Salary: 80.000 EUR

etc

I of course first load and then begin working with set/let and loops. However, how can I the script to initially set my day variable to the lowest date in the input record (i.e. 15.03.2014), on an employee-per-employee basis?

Many thanks in advance,

[swuehl · Accepted solution]

You can do it using

LOAD *,

         Date(Floor(Monthend(Addmonths([Date from],iterno()-1)))) as MonthEnd

INLINE [

PersNr,Date from,Date to,Cost Center,Salary

4711 Kevin Mueller,15.03.2014, 20.12.2015,2489 Strategy and Business Development,80.000 EUR

]

WHILE MonthStart([Date from],iterno()-1) <= MonthStart([Date to]);

[#3]

Marvellous, thanks!