topic Re: Looping through year-months in QlikView

Looping through year-months

Anonymous — Wed, 29 Apr 2015 07:02:51 GMT

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

Re: Looping through year-months

Ralf-Narfeldt — Thu, 30 Apr 2015 07:54:18 GMT

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.

Re: Looping through year-months

Anonymous — Thu, 30 Apr 2015 08:30:19 GMT

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