topic Re: Dynamically Adding rows in QlikView

Dynamically Adding rows

sat_tok52 — Mon, 02 Feb 2015 10:25:20 GMT

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.

Re: Dynamically Adding rows

avinashelite — Mon, 02 Feb 2015 10:37:20 GMT

can you please elaborate your requirement

Re: Dynamically Adding rows

sat_tok52 — Mon, 02 Feb 2015 10:38:20 GMT

Pls find the updated post.

Re: Dynamically Adding rows

avinashelite — Mon, 02 Feb 2015 11:34:16 GMT

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.

Re: Dynamically Adding rows

avinashelite — Mon, 02 Feb 2015 12:03:19 GMT

Hi satessh,

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

Re: Dynamically Adding rows

sat_tok52 — Mon, 02 Feb 2015 12:19:32 GMT

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.

Re: Dynamically Adding rows

maxgro — Mon, 02 Feb 2015 12:39:18 GMT

script in the attachment

Re: Dynamically Adding rows

avinashelite — Mon, 02 Feb 2015 12:52:00 GMT

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;

Re: Dynamically Adding rows

Anonymous — Mon, 02 Feb 2015 12:55:03 GMT

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.

Re: Dynamically Adding rows

Anonymous — Mon, 02 Feb 2015 12:59:04 GMT

Massimos approach is more elegant

Re: Dynamically Adding rows

its_anandrjs — Mon, 02 Feb 2015 14:15:18 GMT

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

Re: Dynamically Adding rows

sat_tok52 — Tue, 03 Feb 2015 06:57:06 GMT

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