Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a summary table as follows;
Product Category | ASP | Units | Rows |
Category A | 120.00 | 1389 | 3 |
Category B | 26.67 | 703 | 4 |
Category C | 191.25 | 784 | 2 |
I want to explode the table to a new table with number of rows for each Product Category as defined in the Rows column.
Required:
Product Category | Code | ASP | Units |
Category A | 1 | 120.00 | 1389 |
Category A | 2 | 120.00 | 1389 |
Category A | 3 | 120.00 | 1389 |
Category B | 4 | 26.67 | 703 |
Category B | 5 | 26.67 | 703 |
Category B | 6 | 26.67 | 703 |
Category B | 7 | 26.67 | 703 |
Category C | 8 | 191.25 | 784 |
Category C | 9 | 191.25 | 784 |
Category A has 3 rows.. Category B as 4 etc.
In the new detailed table I also want to create a new field (Code) which can be any sequential number series.
Anybody any ideas how to achieve this?
Thanks
Maybe like this:
LOAD *, iterno() as Code INLINE [
Product Category, ASP, Units, Rows
Category A, 120.00, 1389, 3
Category B, 26.67, 703, 4
Category C, 191.25, 784, 2
] while iterno() <= Rows;
Maybe like this:
LOAD *, iterno() as Code INLINE [
Product Category, ASP, Units, Rows
Category A, 120.00, 1389, 3
Category B, 26.67, 703, 4
Category C, 191.25, 784, 2
] while iterno() <= Rows;
Thanks for that
Works a treat