Using For/Next in Script

[Anonymous]

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

  Next

DROP Table Projection;

DROP table Percen;

[swuehl · Accepted solution]

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;

[Anonymous]

Thanks swuehl‌ Everything worked fine here!

This mode is much more simpler and it works well.