Hi all,
I have the below table (only chosen 5 rows from 300,00 rows)
Row id Current Item Number lines units Cumulative %SKU %Lines First SKU Last SKU
1 1 14 39 217 217 58 50 1 60
2 1 6 5 8 225 60 54 61 120
3 1 7 5 5 230 73 59 121 190
4 1 1 1 2 232 75 63 191 290
5 1 3 1 1 233 80 73 291 333
Im able to copy the above table to a new table and copy and add n number of rows, ie growing the product range from 300,000 to 350,000.
But I dont want to randomly choose any row, and this is not representative of the dataset. it may copy an unpopular item.
The logic i have in mind is to randomly pick a number between 1 and 100. if that number is within the %Lines "band" then it looks at the first and last sku. it then picks a random number between that first and last sku, goes down the list of the source dataset, picks out that row and copies that. and does a calc to replace Current from 1 to 0 and a few more cals that i'll be able to do.
so.......for example
The above table has 300,000 records (Table A). All current product range (Current = 1).
if Random number selected = 53 it looks at Row ID 1
If random number selected = 62 it looks at Row ID 3 and so on.
so random number selected is 72, it looks at ROW ID 4, First SKU = 191, Last SKU = 290
it then picks a random number between 191 and 290, so for example 277.
it looks at ROW ID 277 in TABLE A, copies that into TABLE B and sets Current = 0. so TABLE B looks like
Row id Current Item Number lines units Cumulative %SKU %Lines First SKU Last SKU
1 1 14 39 217 217 58 50 1 60
2 1 6 5 8 225 60 54 61 120
3 1 7 5 5 230 73 59 121 190
4 1 1 1 2 232 75 63 191 290
5 1 3 1 1 233 80 73 291 333
.
.
277 1 etc etc
.
.
300000 1 etc etc <<<< last row of original TABLE A dataset
300001 0 <<<< copy of row 277 and now current = 0 as its a future product range
i've declared a value in exec statement @IncreaseBy = 50,000 so TABLE B will have 50,000 "new" copied items with Current = 0 using above logic to determined which row to copy.
What do you think??
Any help is much appreciated.
Thanks, Taj