QV Script: Cycles vs Arithmetic Progression with Power Equations

lylererger — Wed, 12 Aug 2015 15:10:56 GMT

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.

Re: QV Script: Cycles vs Arithmetic Progression with Power Equations

lylererger — Sun, 06 Sep 2015 15:10:52 GMT

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 )))

topic Re: QV Script: Cycles vs Arithmetic Progression with Power Equations in QlikView

QV Script: Cycles vs Arithmetic Progression with Power Equations

Re: QV Script: Cycles vs Arithmetic Progression with Power Equations