4 Replies Latest reply: Dec 19, 2013 2:24 PM by Drew Stovall

# While Loop & Variables

I'm trying to get our clients' life cycles into one table that shows all of their terms.  I have the following data points:

• Start Date
• Initial Term Months
• Renewal Term Months
• Extension Date
• Extension Months

Initial Term = Start Date + Initial Term Months + If (Extension Date < (Start Date + Initial Term Months), Extension Months, 0)

Renewal Terms = Last Term End + Renewal Term Months + If (Extension Date > Last Term End AND Extension Date < Last Term End + Renewal Term Months, Extension Months, 0)

The logic is very simple but I'm having a problem pulling in the prior calculated values.  My R.Initial Table already has the initial term calculated.  The logic is that I want the script to continue to run until the client's Current Term's end date is greater than today and then move onto the next client.

Everything I have works except for getting the variables to update with each successive row.  Once I solve that everything else will work.

Renewals:

IterNo() as R.TermNumber,

If(IterNo() = 1, R.InitialTermEnd,AddMonths(\$(vPriorTermEnd),R.RenTerm...) as R.TermEnd,

RowNo() as R.RowNumber,

Set \$(vPriorTermEnd) = If(IterNo() = 1, R.InitialTermEnd,AddMonths(\$(vPriorTermEnd),R.RenTerm...), //Same formula as R.TermEnd

Set \$(vCurrentFlag) = If(If(IterNo() = 1, R.InitialTermEnd,AddMonths(\$(vPriorTermEnd),R.RenTerm...) > Today(), 'Current Term','Prior Term')

Resident R.Initial Until \$(vCurrentFlag) = 'Current Term';

Drop Table R.Initial;

• ###### Re: While Loop & Variables

Can you post some sample input data and the resulting expected output table?

• ###### Re: While Loop & Variables

Example A is the simple example.  Example B uses a 12 month extension that occurred in term 3(notice a 3 year term instead of 2).  Example A is simple and can be figured out using just a formula, but the only way I can figure out Example B is by using a loop and checking for the extension during every term.

 Client Start Date Inititial Term Months Renewal Term Months Extension Date Extension Months Example A 1/31/2008 24 12 Example B 7/15/2005 24 24 10/31/2008 12

 Client Term Number Term Start Term End Row Number Example A 1 1/31/2008 1/30/2010 1 Example A 2 1/31/2010 1/30/2011 2 Example A 3 1/31/2011 1/30/2012 3 Example A 4 1/31/2012 1/30/2013 4 Example A 5 1/31/2013 1/30/2014 5 Example A 6 1/31/2014 1/30/2015 6 Example B 1 7/15/2005 7/14/2007 7 Example B 2 7/15/2007 7/14/2010 8 Example B 3 7/15/2010 7/14/2012 9 Example B 4 7/15/2012 7/14/2014 10
• ###### Re: Re: While Loop & Variables

Something like attached qvw. Figure out when to halt the iteration yourself . I just hardcoded 10 iterations for the example.

• ###### Re: While Loop & Variables

Perfect, thanks.  New user to QlikView and had tried using peek but wasn't getting it to work correctly.