Dynamically Adding rows

[sat_tok52]

Hi All,

I have year ,Contract term and Amount columns .

Year	contract term	Amount
2013	3 years	100

if contract term =3 years . Qlikview Should generate additional two rows,that is 2014,2015 as below.

Header 1
Year contract term Amount 2013 3 years 100 2014 2 years of 3 100 2015 3 years of 3 100

How to achieve this in qlikview thanks in advance.

[its_anandrjs · Accepted solution]

Hi Sateesh,

Load your table and find the max value for the each row and then iterate the row until for max number of year value for more see the load script.

Tab1:
LOAD Trim(KeepChar([contract term],0123456789)) as MaxNum,*;
LOAD * INLINE [
Year, contract term, Amount
2013, 3 years, 100
2014, 5 years, 200
];
 
NoConcatenate
LOAD
RowNo() as ID,
Year,
[contract term],
Amount,
MaxNum,
if(Year = Peek('Year') , IterNo()&' years of '&MaxNum , MaxNum& ' years' ) as [contract term Details],
if(Year = Peek('Year') , Year+IterNo()-1,Year) as NewYear
Resident Tab1 While IterNo() <= MaxNum Order By Year,[contract term] asc;

DROP Table Tab1;
DROP Field MaxNum;

Output you get this way

Regards,

Anand

[sat_tok52]

Hi Anand,

Thanks for your time,It's working fine but in contract term details column should show like below format.

   3 years

  year 2 of 3

Year 3 of 3

Regards,

Sateesh