Using For/Next in Script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

Model	REGION	Number	Year
Z100	APAC	5	2015
Z200	CSA	1	2016

Percen:

Model	REGION	Month	Percentage %
Z100	APAC	jan	60
Z100	APAC	fev	30
Z200	CSA	fev	80

Future: (Result)

Model	REGION	Year	Month	Serial No (RowNo)
Z100	APAC	2015	JAN	1
Z100	APAC	2015	JAN	2
Z100	APAC	2015	JAN	3
Z100	APAC	2015	FEV	4
Z100	APAC	2015	FEV	5
Z200	CSA	2016	FEV	6

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:

Load

Model__c,

REGION,

'$(Year)' as Year,

Month,

RowNo() as [S/N]

Resident Percen;

Next t

DROP Table Projection;

DROP table Percen;

Re: Using For/Next in Script

swuehl — Wed, 02 Mar 2016 19:45:53 GMT

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:

load * inline [
Model, REGION, Number, Year
Z100, APAC, 5, 2015
];

Percen:
LEFT JOIN
load * inline [
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;

Re: Using For/Next in Script

Anonymous — Thu, 03 Mar 2016 11:59:52 GMT

Thanks swuehl‌ Everything worked fine here!

This mode is much more simpler and it works well.

topic Using For/Next in Script in QlikView

Using For/Next in Script

Re: Using For/Next in Script

Re: Using For/Next in Script