QV Script: Cycles vs Arithmetic Progression with Power Equations

[lylererger]

Hi friends

That topic is the continuation of the dialogue: QV Script: Cycles vs Arithmetic Progression‌

Swuehl helps me to calculate ariphmetic progression, this ruled out the time on numerous iterations of cycles.

Not a lot about what results we have achieved:

We have some death table:

tmp:
LOAD * Inline
[
Age Man Woman
0 1000000 1000000
1 999252 1000000
2 998504 1000000
3 997757 1000000
4 997011 1000000
5 996265 1000000
6 995520 1000000
7 994775 1000000
8 994031 1000000
9 993287 1000000
10 992544 1000000
11 991802 1000000
12 991060 1000000
13 990318 1000000
14 989577 1000000
15 988837 1000000
16 988097 999529
17 987351 999045
18 986597 998546
19 985833 998027
20 985058 997488
21 984271 996923
22 983470 996331
23 982652 995709
24 981818 995054
25 980965 994363
26 980092 993634
27 979198 992863
28 978280 992048
29 977337 991186
30 976369 990274
31 975373 989311
32 974343 988292
33 973268 987215
34 972136 986076
35 970933 984875
36 969647 983608
37 968262 982272
38 966763 980865
39 965135 979385
40 963361 977829
41 961424 976194
42 959307 974478
43 956990 972678
44 954455 970792
45 951683 968833
46 948655 966825
47 945349 964785
48 941747 962724
49 937826 960646
50 933568 958551
51 928950 956430
52 923954 954268
53 918557 952046
54 912740 949736
55 906485 947307
56 899770 944720
57 892579 941932
58 884893 938892
59 876697 935547
60 867957 931836
61 858620 927695
62 848630 923055
63 837926 917843
64 826444 911981
65 814116 905392
66 800874 897994
67 786644 889702
68 771353 880421
69 754926 870049
70 737293 858480
71 718384 845601
72 698135 831295
73 676491 815441
74 653407 797921
75 628857 778618
76 602829 757421
77 575338 734232
78 546428 708970
79 516175 681581
80 484692 652039
81 452136 620362
82 418706 586616
83 384648 550929
84 350255 513493
85 315858 474573
86 281824 434514
87 248547 393733
88 216427 352722
89 185863 312029
90 157226 272242
91 130843 233965
92 106978 197783
93 85813 164230
94 67439 133754
95 51847 106685
96 38936 83213
97 28520 63376
98 20347 47061
99 14117 34026
100 9512 23921
101 6217 16333
102 3936 10820
103 2411 6947
104 1428 4321
105 817 2602
106 451 1516
107 240 855
108 123 467
109 61 247
110 29 126
111 13 63
112 6 30
113 3 14
114 1 6
115 1 3
116 1 1
117 0 0
118 0 0
] (delimiter is spaces);

Wiht using cycle result will be looks like:

LET Age = 19;

LET n = 20;


LET Max = n - 1;

FOR i = 0 to vMax

     LET lx = Peek(FieldName(Sex,'tmp'),Age+i,'tmp');  

     LET Obyaz3 = Obyaz3 + lx; 

NEXT i

Using Arithmetic Progression result will be looks like:

Let vFieldName = FieldName($(Sex),'tmp');

tmplx:
LOAD 
  Sum([$(vFieldName)]) as lx
Resident [tmp]
Where RecNo() > $(Age) and RecNo() <= $(Age) + $(vMax) + 1;

LET Obyaz4 = ((((vMax+1)/2)*vMax)-(((vMax/2)*vMax) + (vMax/2))) + peek('lx', 0,'tmplx');

Finally, value of "Obyaz3" will be equals value "Obyaz4" (Arithmetic Progression_3.qvw in attachment)

________________________________________________________________________________________________________________

But now we've got much complex task:

LET NumbVyplat = 26;

FOR i = 0 TO NumbVyplat - 1

     LET NewAge = Age + i / m;

     LET r = NewAge - Floor(NewAge);

     LET lx = Peek(FieldName(Sex,'tmpDeathTable'),Floor(NewAge),'tmpDeathTable');

     LET lx1 = Peek(FieldName(Sex,'tmpDeathTable'),Floor(NewAge)+1,'tmpDeathTable');

     LET lx = (1 - r) * lx + r * lx1;

     LET Obyaz = Obyaz + (lx / l0) * (Pow(v,(i / m)));

NEXT i

LET Annuitet_Smert = (1 / m) * Obyaz; //<----RESULT

Thats why i'm writing here again. As you look formula in not so easy:

LET lx = (1 - r) * lx + r * lx1;

LET Obyaz = Obyaz + (lx / l0) * (Pow(v,(i / m)));

On each iteration of cycle - different results. The final result will be on the final iteration.

Most likely the ultimate goal is to avoid cycles...

I know that qlikview script is not procedure language, but...tell me somebody why so looong ???

P.S.: Replacement of "Peek" function do not gave positive result.

All of those calculations i'm calling from procedure.

The lifecycle of calculations takes about 30 hours for 1500 rows!

Something like 7 sec for 1 row, all depends on age.Client data - is 50 000 rows and grows.

We can't show them this performance )))

That's why i'm writing here and hope for yours help.

Any advices by optimization relative to the processing time of the script.

Demo app in attachment (Arithmetic Progression with Power Equations.qvw )

I know that it's not so easy task and probably gonna need a strong knowledge of mathematics.

Any way will be waiting for respond and as usual I'm open for discussions.

Thanks in advance.

[lylererger · Accepted solution]

Hi friends!

I've found solution.

We can use cycle "While" inside LOAD statement with IterNo() counter.

The increase in productivity simply shocks.

For those who faced the same problem - solution in attachment

Good Luck )))