Can I make this code goes faster?

Mon, 15 Feb 2016 18:37:24 GMT

I'm working with a table like the one below, and a lot of variables declared with a "LET"

ID	G	Status	Age	Density	Year
1	M	R	20	80%	2010
2	F	R	26	90%	2012
3	F	A	24	68%	2013

The variables are something like this:

LET P11MR20=0.10; LET P11MR21=0.50; LET P11MR22=0.80; LET P11MR23=0.30; LET P11MR24=0.65; LET P11MR25=0.41;

LET P21MR20=0.90; LET P21MR21=0.50; LET P11MR22=0.20; LET P11MR23=0.70; LET P11MR24=0.35; LET P11MR25=0.59;

And the code does this:

LET vCantFilasAx = noofrows('Table1'); // I need to work through all the registers of my table

For i=0 to $(vCantFilasAx)-1

//I save in a variable the data of each row in my Table1

LET vEmpr= Peek('ID',$(i),'Table1');

LET vStatus= Peek('Status',$(i),'Table1');

LET vGender= Peek('G',$(i),'Table1');

LET vAge=Peek('Age',$(i),'Table1');

LET vDens=Peek('Density',$(i),'Table1');

LET vAfil=Peek('Year',$(i),'Table1');

LET vBP=((2015-$(vAfil))*6);

LET vBC=((2015-$(vAfil))*6)*$(vDens);

IF $(vAge)>24 or $(vAge)<14 then

LET vDens=$(vDens);

LET vBCot=$(vBC);

ELSE

Let SumD1=0;

Let SumD2=0;

Let TSumD1=0;

//For each age starting with the age declared in the row for each employee

For ag=$(vAge) to 25

//I created new variables in order to use them as an algoritm

LET vP11='P11'& '$(vStatus)' & '$(vGender)'& $(vAge);

LET vP21='P21' & '$(vStatus)' & '$(vGender)' & '$(vAge)';

LET DB1_A=($(vDens)*$(vP11))+((1-$(vDens))*$(vP21));

LET DB1_B=1-$(DB1_A);

LET DB2_A=($(DB1_A)*$(vP11))+($(DB1_B)*$(vP21));

LET DB2_B=1-$(DB2_A);

LET DB3_A=($(DB2_A)*$(vP11))+($(DB2_B)*$(vP21));

LET DB3_B=1-$(DB3_A);

LET DB4_A=($(DB3_A)*$(vP11))+($(DB3_B)*$(vP21));

LET DB4_B=1-$(DB4_A);

LET DB5_A=($(DB4_A)*$(vP11))+($(DB4_B)*$(vP21));

LET DB5_B=1-$(DB5_A);

LET DB6_A=($(DB5_A)*$(vP11))+($(DB5_B)*$(vP21)); //let DB6_B=1-$(DB6_A);

LET SumD1=$(DB1_A)+$(DB2_A)+$(DB3_A)+$(DB4_A)+$(DB5_A)+$(DB6_A);

LET SumD2=$(SumD2)+6;

LET TSumD1=$(SumD1)+$(TSumD1);

//Then calculate the fields I'm going to use in one final table

LET vBCot=$(TSumD1)+$(vBC);

LET vDensidad_final=($(vBC)+$(TSumD1))/($(vBP)+$(SumD2));

LET vDensidad=$(vDensidad_final);

LET vEdad=$(vEdad)+1;

END If

Final2:

LOAD

'$(vWorker)' as ID,

'$(vDens)' as FinalDens,

'$(vBCot)' as BimCot

AutoGenerate 1;

LET vBCot=0;

LET vBP=0;

LET vBC=0;

Store Final2 into Final2.qvd (qvd);

The thing is, actually I have to run this until age 65, I have 50 million rows and 312 variables for each age. Table1 is a qvd, and I try this with data of 3million employees and the whole execution took me 35 hrs...

Is there something I'm doing wrong or that i can do better?

Re: Can I make this code goes faster?

swuehl — Tue, 16 Feb 2016 00:30:53 GMT

You should try to avoid iterating with a loop over each table row and peek the values.

Try to load the table, if you need to create a loop based on a record's value like age use the WHILE clause in your load, something like

LOAD Age, iterno() as IterNo

FROM YourTableSource

WHILE Age + Iterno()-1 <= 65;

Iterno() can be used as a counter for each iteration. Then try to implement your transformations using the set of variables as part of the LOAD statements (maybe using the variables or tables, created from the variables).

topic Re: Can I make this code goes faster? in QlikView

Can I make this code goes faster?

Re: Can I make this code goes faster?