Solved: Dynamically Adding rows - Qlik Community

sat_tok52 · ‎2015-02-02

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 · ‎2015-02-02

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

View solution in original post

avinashelite · ‎2015-02-02

can you please elaborate your requirement

sat_tok52 · ‎2015-02-02

Pls find the updated post.

avinashelite · ‎2015-02-02

Hi

try like this in the script:

Test:

Load * Inline

[

YEAR,TERM,AMOUNT

2013,3,100

2014,2,200

];

LET NumRows=0;

LET NumRows=NoOfRows('Test');

For x=1 to $(NumRows);

let iMax=0;

let iMax=FieldValue('TERM',$(x));

FOR i=0 to iMax-1;

//let filesize=filesize( 'NEW_TEST' );

NEW_TEST:

load YEAR+$(i) as NEW_YEAR,TERM as NEW_TERM,AMOUNT as NEW_AMOUNT Resident Test;

NEXT i

next x

drop Table Test;

Please find the attachment for the same.

avinashelite · ‎2015-02-02

Hi satessh,

If you have got the answer please mark this thread as answered .

sat_tok52 · ‎2015-02-02

Thanks for your time almost matching to my requirement but Contract term should be like below

Year Contract term

2013 , 3 years

2014 , 2 year of 3

2015, 3 year of 3

But your application the New_Term showing same for all three years.

maxgro · ‎2015-02-02

script in the attachment

avinashelite · ‎2015-02-02

try like this:

Test:

Load * Inline

[

YEAR,TERM,AMOUNT

2013,3,100

];

LET NumRows=0;

LET NumRows=NoOfRows('Test');

let x=0;

For x=1 to $(NumRows);

let iMax=0;

let iMax=FieldValue('TERM',$(x));

let i=0;

FOR i=0 to $(iMax)-1;

//let filesize=filesize( 'NEW_TEST' );

NEW_TEST:

load YEAR+$(i) as NEW_YEAR,

if($(i)>0,$(i)+1&' Years'&if($(i)>0,' of ')&TERM,TERM & ' Years') as NEW_TERM,AMOUNT as NEW_AMOUNT Resident Test;

NEXT i

next x

drop Table Test;

Anonymous · ‎2015-02-02

In the above code I have tweaked the new term part as below

let iMax=FieldValue('TERM',$(x));

FOR i=0 to iMax-1;

//let filesize=filesize( 'NEW_TEST' );

NEW_TEST:

load YEAR+$(i) as NEW_YEAR,

If ($(i)=0, TERM, $(i)+1 & ' year of '& TERM )&' years' as NEW_TERM,

AMOUNT as NEW_AMOUNT Resident Test;

NEXT i

...........................................................................................................

Please let me know if you need the modified app.

Anonymous · ‎2015-02-02

Massimos approach is more elegant