Create a table with generated numbers

[#1]

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

[#2]

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;

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

[kaushiknsolanki]

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;

  next      

next    

Drop table Max;

Regards,

Kaushik Solanki

[llauses243]

Hi Campell,

This is my offer, pls see image

Good luck, Luis