Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
NEXT
END If
Final2:
LOAD
'$(vWorker)' as ID,
'$(vDens)' as FinalDens,
'$(vBCot)' as BimCot
AutoGenerate 1;
LET vBCot=0;
NEXT
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?
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).