Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Generate table

Hi all,

I have this data:

Load * Inline

[

ID,Discount,StartAmount,LifeOfLoan

101,0.08,1000,5

102,0.05,5000,6

103,0.1,10000,3

];

I wish to use this data to generate this table using the load script.

Table.jpg

It is important that I generate a table showing these yearly figures. Is this possible?

Thanks,

Mike

1 Solution

Accepted Solutions
MVP
MVP

Re: Generate table

HI,

Try like this

Temp:

LOAD

*,IterNo() - 1 AS YearNumber,

If(IterNo() = 1, StartAmount, Rangesum(StartAmount, StartAmount *  Discount * (IterNo() - 1),  (Peek('YearlyTotal') - StartAmount) *  Discount)) AS YearlyTotal

WHILE IterNo() <= LifeOfLoan;

Load * Inline

[

ID,Discount,StartAmount,LifeOfLoan

101,0.08,1000,5

102,0.05,5000,6

103,0.1,10000,3

];

Regards,

Jagan.

2 Replies
MVP
MVP

Re: Generate table

HI,

Try like this

Temp:

LOAD

*,IterNo() - 1 AS YearNumber,

If(IterNo() = 1, StartAmount, Rangesum(StartAmount, StartAmount *  Discount * (IterNo() - 1),  (Peek('YearlyTotal') - StartAmount) *  Discount)) AS YearlyTotal

WHILE IterNo() <= LifeOfLoan;

Load * Inline

[

ID,Discount,StartAmount,LifeOfLoan

101,0.08,1000,5

102,0.05,5000,6

103,0.1,10000,3

];

Regards,

Jagan.

Not applicable

Re: Generate table

Thanks Jagan, that was fast! I had to add LifeOfLoan+1 to the While in order to generate all the loan years, but your answer was otherwise spot on.

Community Browser