Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm struggling with a problem how to convert a from-to-daterange into a monthbased keyfield.
Let me describe the problem
I have a table with the following data:
Cardnumber | Fromdate | Todate | Employee |
---|---|---|---|
12345 | 01-08-2010 | 14-11-2010 | Pete |
12345 | 15-11-2010 | 20-08-2014 | Hank |
12345 | 21-08-2014 | Jill | |
01982 | 01-08-2010 | Kate | |
01231 | 01-08-2010 | 10-07-2014 | Jim |
To use this data I want to create a key based on the situation on the 1st of each month.
The result I'd like is:
Header 1 | Header 2 |
---|---|
01-08-2010-12345 | Pete |
01-09-2010-12345 | Pete |
01-10-2010-12345 | Pete |
01-11-2010-12345 | Pete |
01-12-2010-12345 | Hank |
01-01-2011-12345 | Hank |
01-02-2011-12345 | Hank |
etc.... | etc... |
01-09-2014-12345 | Jill |
01-10-2014-12345 | Jill |
etc.... | etc... |
01-thismonth-12345 | Jill |
I'm looking for a way to script the second table. I've tried for loops, but it's baffling me right now.
Anyone care to give me a direction as to which direction a solution might be found?
Thanks,
Herbert
I think this should do it:
Source:
LOAD *, If(Len(Todate)=0,Today(), Todate) As To_date;
LOAD * INLINE [
Cardnumber, Fromdate, Todate, Employee
12345, 01-08-2010, 14-11-2010, Pete
12345, 15-11-2010, 20-08-2014, Hank
12345, 21-08-2014, , Jill
01982, 01-08-2010, , Kate
01231, 01-08-2010, 10-07-2014, Jim
];
Monthly:
LOAD
MonthStart(AddMonths(Fromdate,IterNo()-1)) & '-' & Cardnumber As Header1,
Employee as Header2
RESIDENT Source While AddMonths(Fromdate,IterNo()-1) < To_date;
In the source load, I add a field To_date that adds the current date if Todate is missing, to cover the ones that are still open (Jill & Kate). Just to make the check easier in next step. You may need to adapt this depending on your data source if you receive Null values.
The Monthly load is an iterative load. It reads each source record several times, until the condition is met (While AddMonths(Fromdate,IterNo()-1) < To_date). Then it moves on to next record/employee. The IterNo() function keeps track of which loop you are in.
I think this should do it:
Source:
LOAD *, If(Len(Todate)=0,Today(), Todate) As To_date;
LOAD * INLINE [
Cardnumber, Fromdate, Todate, Employee
12345, 01-08-2010, 14-11-2010, Pete
12345, 15-11-2010, 20-08-2014, Hank
12345, 21-08-2014, , Jill
01982, 01-08-2010, , Kate
01231, 01-08-2010, 10-07-2014, Jim
];
Monthly:
LOAD
MonthStart(AddMonths(Fromdate,IterNo()-1)) & '-' & Cardnumber As Header1,
Employee as Header2
RESIDENT Source While AddMonths(Fromdate,IterNo()-1) < To_date;
In the source load, I add a field To_date that adds the current date if Todate is missing, to cover the ones that are still open (Jill & Kate). Just to make the check easier in next step. You may need to adapt this depending on your data source if you receive Null values.
The Monthly load is an iterative load. It reads each source record several times, until the condition is met (While AddMonths(Fromdate,IterNo()-1) < To_date). Then it moves on to next record/employee. The IterNo() function keeps track of which loop you are in.
Thanks Ralf,
The combination of load and while using iterno is a neat trick! And then there's addmonth, i shouldve found that myuself.
I can see myself using in quite a few times in the future.
Thanks a lot,
Herbert