Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hcavanhoogdalem
New Contributor III

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
Employee
Employee

Re: Looping through year-months

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.

2 Replies
Employee
Employee

Re: Looping through year-months

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.

hcavanhoogdalem
New Contributor III

Re: Looping through year-months

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

Community Browser