Autogenerate quarters

[kash04kk]

Hi everyone,

I would like to autogenerate the quarter end of the dates. Currently, i'm using them in script like this:

load * inline

[valuation_date

3/31/2011

6/30/2011

9/30/2011

12/31/2011

3/31/2012

6/30/2012

9/30/2012

12/31/2012

3/31/2013

6/30/2013

9/30/2013

12/31/2013

3/31/2014

6/30/2014

9/30/2014

12/31/2014

3/31/2015

];

Thanks and Regards,

Kashif

[Kushal_Chawda]

Use QuarterEnd() Function

try below script in QlikView

LOAD *,

Year(Date) as Year,

QuarterStart(Date) as QuarterStart,

QuarterEnd(Date) as QuarterEnd;

LOAD date(MakeDate(2013)+IterNo()-1) as Date,

ceil(Rand()*100+1000) as Amount

AutoGenerate(1)

While MakeDate(2013)+IterNo()-1<=Today();

[Anonymous · Accepted solution]

Or maybe like this :

set vStartDate=date(date#('3/31/2011','M/DD/YYYY')) ;

set vNoQuarters = 17 ;

QuarterEnd:

Load

  addmonths($(vStartDate),(iterno()-1)*3) as QuarterEnd

autogenerate 1  while iterno()<= $(vNoQuarters)

;

[kash04kk]

Hey Bill,

This script worked. Thanks a lot!

Can you explain briefly what does iterno() mean?

[Anonymous]

The while iterno()<= $(vNoQuarters) means that each record will be evaluated until iterno() is greater than $(vNoQuarters).

During these iterations iterno() returns 1 for the first record and then increments by 1 for each record, i.e. it acts as a counter.

[kash04kk]

will iterno() always return 1 for the first record?

[Anonymous]

Yup.

See entry from QV Desktop Help I have pasted below.

IterNo( )

This function is only meaningful if used together with a while clause, see Load. IterNo( ) returns an integer indicating for which time one single record is evaluated in a load statement with a while clause. The first iteration has number 1.

[kash04kk]

Thanks a lot Bill. Really appreciate the help.