Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating Multiple Rows Based on Column Value

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.

ContractNumber of years
1a3
2a2

ContractYear
1a1
1a2
1a3
2a1
2a2

Any help would be appreciated

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

While IterNo() <= Nr;

View solution in original post

7 Replies
antoniotiman
Master III
Master III

HI Steve,

LOAD * From Table1;

Left Join LOAD * From Table2;

Regards,

Antonio

Not applicable
Author

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
Master III
Master III

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

While IterNo() <= Nr;

Not applicable
Author

Brilliant

Thank you Antonio

antoniotiman
Master III
Master III

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
Partner - Creator III
Partner - Creator III

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;

Not applicable
Author

That works too

Thanks Florentina