Hi All,
I hope this is basic; just need a nudge in the right direction.
I have read in a database table from MS Access into a data frame using RODBC. Here is a basic structure of what I read in:
PRODID PROD Year Week QTY SALES INVOICE
Here is the structure:
str(data)
'data.frame': 8270 obs. of 7 variables:
$ PRODID : int 20001 20001 20001 100001 100001 100001 100001 100001 100001 100001 ...
$ PROD : Factor w/ 1239 levels "1% 20qt Box",..: 335 335 335 128 128 128 128 128 128 128 ...
$ Year : int 2010 2010 2010 2009 2009 2009 2009 2009 2009 2010 ...
$ Week : int 12 18 19 14 15 16 17 18 19 9 ...
$ QTY : num 1 1 0 135 300 270 300 270 315 315 ...
$ SALES : num 15.5 0 -13.9 243 540 ...
$ INVOICES: num 1 1 2 5 11 11 10 11 11 12 ...
Here are the top few rows:
head(data, n=10)
PRODID PROD Year Week QTY SALES INVOICES
1 20001 Dolie 12" 2010 12 1 15.46 1
2 20001 Dolie 12" 2010 18 1 0.00 1
3 20001 Dolie 12" 2010 19 0 -13.88 2
4 100001 Cage Free Eggs 2009 14 135 243.00 5
5 100001 Cage Free Eggs 2009 15 300 540.00 11
6 100001 Cage Free Eggs 2009 16 270 486.00 11
7 100001 Cage Free Eggs 2009 17 300 540.00 10
8 100001 Cage Free Eggs 2009 18 270 486.00 11
9 100001 Cage Free Eggs 2009 19 315 567.00 11
10 100001 Cage Free Eggs 2010 9 315 569.25 12
I simply want to generate lags for QTY, SALES, INVOICE for each product but I am not sure where to start. I know R is great with Time Series, but I am not sure where to start.
I have two questions:
1- I have the raw invoice data but have aggregated it for reporting purposes. Would it be easier if I didn't aggregate the data?
2- Regardless of aggregation or not, what functions will I need to loop over each product and generate the lags as I need them?
In short, I want to loop over a set of records, calculate lags for a product (if possible), append the lags (as they apply) to the current record for each product, and write the results back to a table in my database for my reporting software to use.
Any help you can provide will be greatly appreciated!
Many thanks in advance,
Brock
