Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

  Load

  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
swuehl
MVP
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:

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;

View solution in original post

2 Replies
swuehl
MVP
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:

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
Not applicable
Author

Thanks swuehl‌ Everything worked fine here!

This mode is much more simpler and it works well.