While Loop & Variables

[#1]

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:

NoConcatenate Load *,

     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;

[Gysbert_Wassenaar]

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

[#3]

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

[Gysbert_Wassenaar · Accepted solution]

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

[#5]

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