Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Can I make this code goes faster?

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

IDGStatusAgeDensityYear
1MR2080%2010
2FR2690%2012
3FA2468%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?

1 Reply
MVP
MVP

Re: Can I make this code goes faster?

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

Community Browser