7 Replies Latest reply: Oct 24, 2011 5:24 AM by Sokkorn Cheav RSS

How to generate records?

Isaac Li

Hi All,

 

I have one record:

 

ID, StartDate

1, 2011-10-01

 

Now I want to generate it 5 times, but the StartDate must be changed.

 

It looks like this:

ID, StartDate

1, 2011-10-01

1, 2011-11-01

1, 2011-12-01

1, 2012-01-01

1, 2012-02-01

 

How to generate them?

 

Many thanks.

  • Re: How to generate records?
    Halmar Heijnen

    Hi, Try this: let varMinDate = num(makedate(2011,10,1)); let varMaxDate = num(makedate(2011,10,5)); Temp2: load 1                 as ID, $(varMinDate)+IterNo()-1 AS Num, Date($(varMinDate)+IterNo()-1) AS TempDate AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate); succes! Halmar

  • Re: How to generate records?
    Olaf Joppe

    The trick I think is to use the AutoGenerate function, in combination with AddMonths. How about this:

     

    SET DateFormat='YYYY-MM-DD';

    SET vStartDate = '2011-10-01';

    GeneratedDates:
    LOAD
        RecNo() as [ID]
      , If(RecNo()=1,
          '$(vStartDate)',
          Date(AddMonths( Date(Peek('StartDate')), 1))
                )
                as [StartDate]
      AutoGenerate 5;

    • Re: How to generate records?
      Isaac Li

      Thanks for your reply,

       

      But the records are from DB and more than one record, how to achieve it?

       

      ID, StartDate

      1, 2011-10-01

      2, 2011-11-01

      • Re: How to generate records?
        Olaf Joppe

        I'm not sure if I understand you correctly, but the function RecNo() as [ID] will generate a unique record number per record generated. The '5' in the AutoGenerate function will generate exactly 5 records. You can adjust it to your needs.

      • Re: How to generate records?
        Isaac Li

        I understand, but I mean if the source records are two not one, how to generate them?

         

        Then the results are 10.

         

        For example:

         

        ID, StartDate

        1, 2011-10-01

        2, 2011-11-01

         

         

        It looks like this:

        ID, StartDate

        1, 2011-10-01

        1, 2011-11-01

        1, 2011-12-01

        1, 2012-01-01

        1, 2012-02-01

         

        2, 2011-11-01

        2, 2011-12-01

        2, 2012-01-01

        2, 2012-02-01

        2, 2012-03-01

         

         

        How to achieve it?

        • Re: How to generate records?
          Olaf Joppe

          Then it becomes a bit more complex. I would put the LOAD ... AutoGenerate in a loop per every unique StartDate you get. In this example the list of start dates is inline, but they could easily come from a datasource ofcourse.

          SET DateFormat='YYYY-MM-DD';
          
          ListOfStartDates:
          LOAD * Inline [
            nuID, nuStart
            1,    '2011-10-01'
            2,    '2011-11-01'
          ];
          
          FOR x = 1 to FieldValueCount('nuStart')
          
            LET vID = FieldValue('nuID', $(x));
            LET vStartDate = FieldValue('nuStart', $(x));
            
            GeneratedDates:
            LOAD
                '$(vID)'                as [ID]
              , If(RecNo()=1
                  ,'$(vStartDate)'
                  ,Date(AddMonths( Date(Peek('StartDate')), 1)) )
                                        as [StartDate]
              AutoGenerate 5;
          
          NEXT x
          
          DROP Table ListOfStartDates;
              
      • Re: How to generate records?
        Sokkorn Cheav

        Hi,

        Try this script

        [Data]:
        LOAD * INLINE [
        ID,StartDate
        1,01-10-2011
        2,01-11-2011];
        
        SET vRecCount = NoOfRows('Data')-1;
        FOR i = 0 TO $(vRecCount)
            vStartDate = NUM(PEEK('StartDate',$(i),'Data'));
            vID = PEEK('ID',$(i),'Data');
        
            Data2:
            LOAD DISTINCT
            $(vID) AS ID,      
            If(RecNo()=1,'$(vStartDate)',Date(AddMonths(Date(Peek('StartDate')), 1))) AS [StartDate]
            AUTOGENERATE 5;
        NEXT
        

        Check more on attahced file.