IterNo can be done monthwise?

[#1]

Dear QVExperts,

Can IterNo() can be done monthwise?

The below script load datewise & the certificate validity for one year then 365 rows created.

Suppose if it is monthwise, then we will have either 12 or 13 rows maximum.

LOAD

CERT_SQN,

EXT_WRNTY_STR_DT,

EXT_WRNTY_END_DT,

Date(EXT_WRNTY_STR_DT+iterno()-1) as IterDate,

MonthName(Date(EXT_WRNTY_STR_DT+iterno()-1)) as MonthYear,

PREMIUM_DUE_AIG_AM / (EXT_WRNTY_END_DT - EXT_WRNTY_STR_DT  +1) as DailyAmount,

BAND_SQN

while EXT_WRNTY_STR_DT+iterno()-1 <= EXT_WRNTY_END_DT;

LOAD

CERT_SQN,

EXT_WRNTY_STR_DT,

EXT_WRNTY_END_DT,

PREMIUM_DUE_AIG_AM,

BAND_SQN

FROM [..\..\DATA\QVD\ITERDATE_CERT1_UAE_0526.QVD]

(qvd);

Can you please suggest me?

Thanks,

Sasi

[sunny_talwar]

May be like this:

LOAD

CERT_SQN,

EXT_WRNTY_STR_DT,

EXT_WRNTY_END_DT,

Date(EXT_WRNTY_STR_DT+iterno()-1) as IterDate,

MonthName(AddMonths(EXT_WRNTY_STR_DT, iterno()-1)) as MonthYear,

PREMIUM_DUE_AIG_AM / (EXT_WRNTY_END_DT - EXT_WRNTY_STR_DT  +1) as DailyAmount,

BAND_SQN

While AddMonths(EXT_WRNTY_STR_DT, iterno()-1) <= EXT_WRNTY_END_DT;

LOAD

CERT_SQN,

EXT_WRNTY_STR_DT,

EXT_WRNTY_END_DT,

PREMIUM_DUE_AIG_AM,

BAND_SQN

FROM [..\..\DATA\QVD\ITERDATE_CERT1_UAE_0526.QVD]

(qvd);

[jonathandienst]

You may want to wrap the IterNo() calculation inside an AddMonths function. Something like

while AddMonths(EXT_WRNTY_STR_DT, iterno()-1) <= EXT_WRNTY_END_DT;

[#4]

Thanks Sunny,

Requiring only four columns, CERT_SQN, MonthYear, MonthlyPremium,BAND_SQN.

I am using the code like below but i am not getting the monthly premium correctly.

LOAD

CERT_SQN,

MonthName(Date(EXT_WRNTY_STR_DT+iterno()-1)) as MonthYear,

SUM(PREMIUM_DUE_AIG_AM / (EXT_WRNTY_END_DT - EXT_WRNTY_STR_DT  +1)) as Amount,

BAND_SQN

while EXT_WRNTY_STR_DT+iterno()-1 <= EXT_WRNTY_END_DT

GROUP BY CERT_SQN,MonthName(Date(EXT_WRNTY_STR_DT+iterno()-1)), BAND_SQN ;

LOAD

CERT_SQN,

EXT_WRNTY_STR_DT,

EXT_WRNTY_END_DT,

PREMIUM_DUE_AIG_AM,

BAND_SQN

FROM

[..\..\DATA\QVD\ITERDATE_CERT1_UAE_0526.QVD]

(qvd);

Please suggest me.

[#5]

Sunny - I am not getting the Monthly Premium & it is displaying DailyPremium

Only 4 columns required.

CERT_SQN, MonthYear, BAND_SQN, MonthlyPremium

Please suggest me.

Thanks,

Sasi