topic For loop in load Script / Generate new Rows in QlikView

For loop in load Script / Generate new Rows

johann_bauer — Wed, 19 Oct 2016 10:01:57 GMT

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.

Johann

Re: For loop in load Script / Generate new Rows

johann_bauer — Wed, 19 Oct 2016 12:00:24 GMT

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;

Re: For loop in load Script / Generate new Rows

sunny_talwar — Wed, 19 Oct 2016 16:48:39 GMT

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);