Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am extracting data from a SQL database and am wanting to combine a number of fields into one, which would have to be grouped by a period (see Table1 below). The below shows a forecast quantity per week for each product and rather than having a seperate field per week I want to combine them and then create a new field which would now be the week (e.g. Table2). The problem I have is that the database has data from 2000 up until 2015, and this will continue to expand over time, and my initial solution of concatenating a week at a time would not be sustainable or efficient.
Thanks,
Ralph
Table1:
Product | Region | 201335 | 201336 | 201337 | 201338 | 201339 | 201340 | 201341 | 201342 |
---|---|---|---|---|---|---|---|---|---|
Chicken | UK | 30 | 35 | 30 | 34 | 14 | 35 | 40 | 21 |
Eggs | UK | 400 | 400 | 500 | 350 | 400 | 300 | 450 | 140 |
Beef | US | 35 | 55 | 55 | 55 | 60 | 23 | 45 | 34 |
Table2:
Product | Region | Week | Quantity |
---|---|---|---|
Chicken | UK | 201335 | 30 |
Eggs | UK | 201335 | 400 |
Beef | US | 201335 | 35 |
Chicken | UK | 201336 | 35 |
Eggs | UK | 201336 | 400 |
Beef | US | 201336 | 55 |
Chicken | UK | 201337 | 30 |
Eggs | UK | 201337 | 500 |
Beef | US | 201337 | 55 |
try to use
crosstable(week,Quantity,2)
See the attached file
Thanks, I didn't even think about using a Crosstable.
Thanks,
Ralph