Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

do until to create a field in my data

Hi I am new to click view and I am a SAS programmer. I am trying to replicate this "do until" in Click view to create a field renewal_date

The else if part and I am struggling with as I want the field to add 1 year until it is greater than or equal to the pv_edate field.

note this is SAS code; Intnx is like ADDYEAR.

  Renewal_Date=intnx('year',pol_startDate,1,'sameday');

  If pv_stat=2           then do;

                                Renewal_Date=Renewal_DAte 

                             end;

  else if pv_stat in (3,4,5,6) then do;

                                do until (Renewal_Date>=pv_edate);

                                       Renewal_Date=intnx('year',Renewal_Date,1,'sameday');

                                end;   

for example:

Renewal_Date for  Row 1 of data is set as   25MAR2011

PV_EDATE is Set too                               05JUL2013

The result will mean that after the do until the Renewal_date will change too 22MAR2014.

1 Solution

Accepted Solutions
Not applicable
Author

Thanks for this String it seems logical, I have wriiten this statement based on your suggestion. Although I not familar with the DayNumberOfYear function. Is the number of days in the calander year?

LOAD

IF(PolicyVersionStatusId=2,TempRenewalDate,
Addyears(TempRenewalDate, if(DayNumberOfYear(TempRenewalDate)>DayNumberOfYear( PolicyVersionEndDate),0,1) + year( PolicyVersionEndDate)-year(TempRenewalDate))) as RenewalDate,

LOAD

ADDYEARS (date(num(floor(InitialStartDate)),'dd/MM/yyyy'),1) asTempRenewalDate

,

In this example however the RenewalDate is coming out as  11/01/2014 rather than 11/01/2013

 

PolicyStartDate

 

PolicyVersionStartDate

 

PolicyVersionEndDate

 

PolicyEndDate

 

RenewalDate

 

11/01/2011

 

02/01/2014

 

11/01/2013

 

11/01/2013

 

11/01/2014

I think the string needs modifying as it doesnt take care of the -- do until greater "or equal part".

I have added an = part as such

(DayNumberOfYear(TempRenewalDate)>=DayNumberOfYear( PolicyVersionEndDate),0,1)

but I am not sure is this is correct or not. It resolves this particular record.

I am however finding a lot of differences in terms of the resulting renewal date from my orignal code to that of the formula in qlikview.


View solution in original post

3 Replies
Gysbert_Wassenaar

Maybe something like this:

LOAD

FieldA, FieldB, ...., FieldX,

addyears(Renewal_Date, if(DayNumberOfYear(Renewal_Date)>DayNumberOfYear(PV_EDATE),0,1) + year(PV_EDATE)-year(Renewal_Date)) as Renewal_Date

FROM MySource;

If the dates in your source are string values you'll have to use the date# function to turn them into real dates: date#(Renewal_Date,'DDMMMYYYY')


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks for this String it seems logical, I have wriiten this statement based on your suggestion. Although I not familar with the DayNumberOfYear function. Is the number of days in the calander year?

LOAD

IF(PolicyVersionStatusId=2,TempRenewalDate,
Addyears(TempRenewalDate, if(DayNumberOfYear(TempRenewalDate)>DayNumberOfYear( PolicyVersionEndDate),0,1) + year( PolicyVersionEndDate)-year(TempRenewalDate))) as RenewalDate,

LOAD

ADDYEARS (date(num(floor(InitialStartDate)),'dd/MM/yyyy'),1) asTempRenewalDate

,

In this example however the RenewalDate is coming out as  11/01/2014 rather than 11/01/2013

 

PolicyStartDate

 

PolicyVersionStartDate

 

PolicyVersionEndDate

 

PolicyEndDate

 

RenewalDate

 

11/01/2011

 

02/01/2014

 

11/01/2013

 

11/01/2013

 

11/01/2014

I think the string needs modifying as it doesnt take care of the -- do until greater "or equal part".

I have added an = part as such

(DayNumberOfYear(TempRenewalDate)>=DayNumberOfYear( PolicyVersionEndDate),0,1)

but I am not sure is this is correct or not. It resolves this particular record.

I am however finding a lot of differences in terms of the resulting renewal date from my orignal code to that of the formula in qlikview.


Not applicable
Author

In My SAS code I perform another step that actually then creates the same result as my previous comment so this statement with the = sign would be correct