Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
----------------------------------------------------------------
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
Hi Campell,
This is my offer, pls see image
Good luck, Luis