Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Looping through year-months

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:

CardnumberFromdateTodateEmployee
1234501-08-201014-11-2010Pete
1234515-11-201020-08-2014Hank
1234521-08-2014Jill
0198201-08-2010Kate
0123101-08-201010-07-2014Jim

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 1Header 2
01-08-2010-12345Pete
01-09-2010-12345Pete
01-10-2010-12345Pete
01-11-2010-12345Pete
01-12-2010-12345Hank
01-01-2011-12345Hank
01-02-2011-12345Hank
etc....etc...
01-09-2014-12345Jill
01-10-2014-12345Jill
etc....etc...
01-thismonth-12345Jill

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

1 Solution

Accepted Solutions
Ralf-Narfeldt
Employee
Employee

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.

View solution in original post

2 Replies
Ralf-Narfeldt
Employee
Employee

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.

Anonymous
Not applicable
Author

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