For loop in load Script / Generate new Rows

[johann_bauer]

Hi Community,

i have a logical problem to create new Rows with a for loop.

I have following origin table:

#	Startdate	Duration	Total Rev
1	01.01.2015	3	30000

#	Months	Year	Revenue
1	Jan	2015	10000
1	Feb	2015	10000
1	Mar	2015	10000

this table need to be created form following origin columns.

At first i tried to use 2 for loops like this:

Let vRows= NoOfRows('oppo');

Let vSize =Peek('Duration',1,'oppo');

For j=1 to vRows

FOR i=1 to $(vSize)

test:

LOAD

     month(addmonths((date#(Startdate,'DD.MM.YYYY')),$(i))) as Monat ,

     year(addmonths((date#(Startdate,'DD.MM.YYYY')),$(i))) as Year,

      #,

     Duration,

     Revenue_Total/Duration as Revenue,

Resident oppo;

NEXT i;

vSize=Peek('Duration',i,'oppo')

Next j;

Well this script didnt worked for me, i think because qlikview does a implicit for loop over all rows. I tried to remove the outer for loop like this.

FOR i=1 to $(vSize)

test:

LOAD

     month(addmonths((date#(Startdate,'DD.MM.YYYY')),$(i))) as Monat ,

     year(addmonths((date#(Startdate,'DD.MM.YYYY')),$(i))) as Year,

      #,

     Duration,

     Revenue_Total/Duration as Revenue,

Resident oppo;

NEXT i;

vSize=Peek('Duration',i,'oppo')

But here is the Problem that the vSize is the same for each row.

Kr

Johann

[johann_bauer]

Hey Guys,

i allready solved it, i used a while loop like this:

LOAD

    month(addmonths((date#(StartDate,'DD.MM.YYYY')),IterNo())) as Monat ,

    year(addmonths((date#(StartDate,'DD.MM.YYYY')),IterNo())) as Year,

   StartDate

Duration ,

    Revenue_Total/Duration as Revenue,

#

Resident oppo

While IterNo()  <= Duration;

[sunny_talwar · Accepted solution]

Try this script out:

Table:

LOAD #,

  Month(AddMonths(Startdate, IterNo()-1)) as Months,

  Year(AddMonths(Startdate, IterNo()-1)) as Year,

  [Total Rev]/Duration as Revenue

While IterNo() <= Duration;

LOAD #,

    Startdate,

    Duration,

    [Total Rev]

FROM

[https://community.qlik.com/thread/236887]

(html, codepage is 1252, embedded labels, table is @1);