# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Creator II

## Using For/Next in Script

Hi all,

Does anyone know how can I solve this?

What I would like to do is go through "Projection" table, take the column Number, Region, Model__c and Year and then compare with table "Percen". If the value is the same, the script generates "Future" table, using "for" clause. Future Table is generated by multiplying the percentage per Number of Z100.

There is another way to see data below:

Projection:

ModelREGIONNumberYear
Z100APAC52015
Z200CSA12016

Percen:

ModelREGIONMonthPercentage %
Z100APACjan

60

Z100APACfev30
Z200CSAfev80

Future: (Result)

ModelREGIONYearMonthSerial No (RowNo)
Z100APAC2015JAN1
Z100APAC2015JAN

2

Z100APAC2015JAN3
Z100APAC2015FEV4
Z100APAC2015FEV

5

Z200CSA2016FEV6

Projection:

load * inline [Model, REGION, Number, Year

Z100, APAC, 5, 2015];

Percen:

load * inline [Model, REGION, Month, Percentage

Z100, APAC, jan, 60

Z100, APAC, fev, 10];

// Calculation

//Let vRows = NoOfRows('Percen')-1;

For i = 0 to NoOfRows('Projection')-1;

Let vNumber = peek('Number',i,'Projection');

Let vRegionF = PurgeChar(peek('REGION',i,'Projection'), chr(39));

Let vModelF = PurgeChar(peek('Model__c',i,'Projection'), chr(39));

Let vYear = peek('Year',i,'Projection');

For z = 0 to NoOfRows('Percen')-1;

Let vRegionPer = PurgeChar(peek('REGION',z,'Percent'), chr(39));

Let vModelPer = PurgeChar(peek('Model__c',z,'Percent'), chr(39));

if '\$(vRegionPer)' = '\$(vRegionF)' and '\$(vModelF)' = '\$(vModelPer)' then

Let vPercentage = peek('Percentage',z,'Percent');

For t = 0 to Round(\$(vNumber) * \$(vPercentage),1)

Future:

Model__c,

REGION,

'\$(Year)' as Year,

Month,

RowNo() as [S/N]

Resident Percen;

Next t

Next

DROP Table Projection;

DROP table Percen;

1 Solution

Accepted Solutions
MVP

Try to avoid FOR NEXT loops with PEEK()ing values, try instead to JOIN your tables or ApplyMap values and the use a WHILE clause, something like

Projection:

Model, REGION, Number, Year
Z100, APAC, 5, 2015
];

Percen:
LEFT JOIN
Model, REGION, Month, Percentage
Z100, APAC, jan, 60
Z100, APAC, fev, 10
];

RESULT:
LOAD *, Rowno() as [S/N] Resident Projection
WHILE iterno() <= ROund(Number*Percentage/100);

DROP TABLE Projection;

2 Replies
MVP

Try to avoid FOR NEXT loops with PEEK()ing values, try instead to JOIN your tables or ApplyMap values and the use a WHILE clause, something like

Projection:

Model, REGION, Number, Year
Z100, APAC, 5, 2015
];

Percen:
LEFT JOIN
Model, REGION, Month, Percentage
Z100, APAC, jan, 60
Z100, APAC, fev, 10
];

RESULT:
LOAD *, Rowno() as [S/N] Resident Projection
WHILE iterno() <= ROund(Number*Percentage/100);

DROP TABLE Projection;

Creator II
Author

Thanks swuehl‌ Everything worked fine here!

This mode is much more simpler and it works well.