Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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:

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;

1 Solution

Accepted Solutions

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.


talk is cheap, supply exceeds demand
4 Replies

Re: While Loop & Variables

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


talk is cheap, supply exceeds demand
Not applicable

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 DateInititial Term MonthsRenewal Term MonthsExtension DateExtension Months
Example A1/31/20082412
Example B7/15/2005242410/31/200812

ClientTerm NumberTerm StartTerm EndRow Number
Example A11/31/20081/30/20101
Example A21/31/20101/30/20112
Example A31/31/20111/30/20123
Example A41/31/20121/30/20134
Example A51/31/20131/30/20145
Example A61/31/20141/30/20156
Example B17/15/20057/14/20077
Example B27/15/20077/14/20108
Example B37/15/20107/14/20129
Example B47/15/20127/14/201410

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.


talk is cheap, supply exceeds demand
Not applicable

Re: While Loop & Variables

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

Community Browser