Creating Multiple Rows Based on Column Value

[#1]

I'm looking to create multiple rows in a QlikView Table based on a column value.

I've got a table with contract numbers and the duration of the contract (Number of years), shown in the first table.

I'd require a row for each year in the contract, shown in the second table.

Contract	Number of years
1a	3
2a	2

Contract	Year
1a	1
1a	2
1a	3
2a	1
2a	2

Any help would be appreciated

[antoniotiman]

HI Steve,

LOAD * From Table1;

Left Join LOAD * From Table2;

Regards,

Antonio

[#3]

Hi Antonio,

Sorry I didn't explain the my problem very well.

I've only got table 1 and I'm looking to create table 2 from it

[antoniotiman · Accepted solution]

LOAD *,IterNo() as Year Inline [
Contract,Nr
1a,3
2a,2
]
While IterNo() <= Nr;

[#5]

Brilliant

Thank you Antonio

[antoniotiman]

If You Want start From Year (e.g. 2015) then

LOAD *,2015+IterNo()-1 as Year Inline [
Contract,Nr
1a,3
2a,2
]
While IterNo() <= Nr;

Regards,

Antonio

[florentina_doga]

use this script

aa:

load * inline [contract, noofyear

1a,3

2a,2

3a,5

a4,9];

let no=NoOfRows('aa');

for i=0 to no-1

    let nocontract=peek('contract',$(i),'aa');

    let noyear=peek('noofyear',$(i),'aa');

    final_tmp:

    load

    '$(nocontract)' as Contract,

    recno()+iterno() as Year

    autogenerate (noyear);

next;

drop table aa;

[#8]

That works too

Thanks Florentina