topic Re: While Loop & Variables in QlikView

While Loop & Variables

Thu, 19 Dec 2013 16:44:06 GMT

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;

Re: While Loop & Variables

Gysbert_Wassenaar — Thu, 19 Dec 2013 17:47:42 GMT

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

Re: While Loop & Variables

Thu, 19 Dec 2013 18:06:20 GMT

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

Example B

7/15/2005

10/31/2008

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

Gysbert_Wassenaar — Thu, 19 Dec 2013 18:43:37 GMT

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

Re: While Loop & Variables

Thu, 19 Dec 2013 19:24:12 GMT

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