Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
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:

  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
Highlighted
MVP
MVP

Re: Using For/Next in Script

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
Highlighted
MVP
MVP

Re: Using For/Next in Script

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

Highlighted
Creator II
Creator II

Re: Using For/Next in Script

Thanks swuehl‌ Everything worked fine here!

This mode is much more simpler and it works well.