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.