topic Re: IterNo can be done monthwise? in QlikView

IterNo can be done monthwise?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: IterNo can be done monthwise?

sunny_talwar — Thu, 26 May 2016 12:26:28 GMT

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);

Re: IterNo can be done monthwise?

jonathandienst — Thu, 26 May 2016 12:26:50 GMT

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;

Re: IterNo can be done monthwise?

Thu, 26 May 2016 12:29:55 GMT

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.

Re: IterNo can be done monthwise?

Thu, 26 May 2016 12:35:07 GMT

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