Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to generate records?

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;
    

View solution in original post

7 Replies
Not applicable
Author

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

Anonymous
Not applicable
Author

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;

Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Not applicable
Author

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?

Anonymous
Not applicable
Author

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;
    
Sokkorn
Master
Master

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.