topic Create a table with generated numbers in QlikView

Create a table with generated numbers

Wed, 20 Jul 2011 08:21:39 GMT

Hi Everyone,

I hope this is a simple fix and someone can help me to autogenerate some numbers.

I have the following table format:

ID, Bucket, Months

A, buck1, 8

B, buck1, 9

C, buck1, 2

A, buck2, 3

B, buck2, 7

C, buck2, 3

I need to create another table on the basis of this one that has a row for each of these from 1 -> Months. So the table above would become the following (as a separate table):

Looking just at ID A as an example, the rows for this ID would be:

ID, Bucket, Months

A, buck1, 1

A, buck1, 2

A, buck1, 3

A, buck1, 4

A, buck1, 5

A, buck1, 6

A, buck1, 7

A, buck1, 8

...

A, buck2, 1

A, buck2, 2

A, buck2, 3

So it's quite basic, just taking the number of months and creating a row for each value from 1 up to that number of months.

Any help is greatly appreciated!!

Thanks

Gareth

Create a table with generated numbers

Wed, 20 Jul 2011 09:51:41 GMT

Just a quick reply: Check the iterNo() function.

Use it with a while loop.

Quick example (Don't know if it's entirely correct):

----------------------------------------------------------------

set vCounter = 1

LOAD ID,

Bucket,

vCounter + IterNo() -1 as Months,

WHILE vCounter + IterNo() - 1 <= Months;

----------------------------------------------------------------

Create a table with generated numbers

kaushiknsolanki — Wed, 20 Jul 2011 10:46:22 GMT

Hi,

Try the below code.

Data:

LOAD * INLINE [

ID, Bucket, Months

A, buck1, 8

B, buck1, 9

C, buck1, 2

A, buck2, 3

B, buck2, 7

C, buck2, 3

];

Max:

load

ID as ID1,

Bucket as Bucket1,

RowNo() as counter,

Max(Months) as Max

Resident Data group by ID,Bucket;

Drop table Data;

let vcount = peek('counter',-1,'Max');

for i= 1 to $(vcount)

let vcount1 = peek('Max',$(i) -1,'Max');

for j = 1 to $(vcount1)

Data:

Load

peek('ID1',$(i) -1,'Max') as ID,

peek('Bucket1',$(i) -1,'Max') as Bucket,

'$(j)' as Months

resident Max;

Drop table Max;

Regards,

Kaushik Solanki

Re: Create a table with generated numbers

llauses243 — Wed, 20 Jul 2011 11:01:25 GMT

Hi Campell,

This is my offer, pls see image

Good luck, Luis