2 Replies Latest reply: Apr 30, 2015 4:30 AM by Herbert van Hoogdalem RSS

    Looping through year-months

    Herbert van Hoogdalem

      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

        • Re: Looping through year-months
          Ralf Narfeldt

          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.