Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
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;
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
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.
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?
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;
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.