topic Re: Creating Multiple Rows Based on Column Value in QlikView

Creating Multiple Rows Based on Column Value

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Creating Multiple Rows Based on Column Value

antoniotiman — Fri, 26 May 2017 09:59:34 GMT

HI Steve,

LOAD * From Table1;

Left Join LOAD * From Table2;

Regards,

Antonio

Re: Creating Multiple Rows Based on Column Value

Fri, 26 May 2017 10:12:19 GMT

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

Re: Creating Multiple Rows Based on Column Value

antoniotiman — Fri, 26 May 2017 10:19:35 GMT

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

Re: Creating Multiple Rows Based on Column Value

Fri, 26 May 2017 10:24:13 GMT

Brilliant

Thank you Antonio

Re: Creating Multiple Rows Based on Column Value

antoniotiman — Fri, 26 May 2017 10:27:28 GMT

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

Re: Creating Multiple Rows Based on Column Value

florentina_doga — Fri, 26 May 2017 10:29:00 GMT

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;

Re: Creating Multiple Rows Based on Column Value

Fri, 26 May 2017 10:33:38 GMT

That works too

Thanks Florentina