topic Re: How to generate records? in QlikView

How to generate records?

Mon, 24 Oct 2011 07:22:57 GMT

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?

Mon, 24 Oct 2011 07:46:16 GMT

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?

Anonymous — Mon, 24 Oct 2011 07:48:52 GMT

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?

Mon, 24 Oct 2011 08:32:45 GMT

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?

Anonymous — Mon, 24 Oct 2011 08:50:08 GMT

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?

Mon, 24 Oct 2011 08:52:33 GMT

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?

Anonymous — Mon, 24 Oct 2011 09:17:58 GMT

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 — Mon, 24 Oct 2011 09:24:17 GMT

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.