<< Any thoughts on an efficient method as to link this continually changing data into Rbase table(s). ? A simple table with Store Number and Item number would have 306150 rows of data. (1950 x 157) and would also be difficult to populate from the above matrix. >>
>From the look of the sample data you displayed, the actual number of rows >would be considerably less. You only need to store a row for the Y cases, the >N cases can be indicated by the absence of a row. Your sample data (if it's >representative) shows just under half the store/item combinations with Y, so >you'd have about 150K rows. This is the "best" and "easiest" way to store the data, I think. You would load the spreadsheet into a temp table, read the first row to get part numbers, and and delete from your part number table, then normalize the data storing StoreNo, ItemNo in a table for those Y combinations. A less ideal solution would be to create a store table with a large text (or note field) holding a string listing all items stocked at that store. The items must be separated by a character GUARANTEED not to be present in the item number, and the separator must appear at the beginning and end of the list. This is to protect against product IDs that nest another product ID inside them For instance: StoreNoItemsStocked 0100|X123|B22|DOORKNOB|1234| or 0100<X123><B22><DOORKNOB><1234> This way you can using CONTAINS to search for product numbers. This solution uses 1950 rows and a TEXT or NOTE column of about ((N/2) * (S+1)) where N is the average number of items stocked per store and S the average length of a product ID. Searches are not indexed but, because the table is pretty small, it will still be fast. If you want to get fancier (and reduce storage the most), every byte of data can store eight Yes/No values. Therefore 256 product Y/N settings (the maximum columns allowed in an Excel spreadsheet) could be encoded in a text column of length 32. You would need UDFs named GetBit and SetBit to do the actual logical manipulation of the text fields. This solution would produce a table with 1950 rows and 2 columns (StoreNo TEXT(4), ProductFlags TEXT(32)). Searches would require the use of GetBit to test each set of flags for the desired product and this would be non-indexed, but because (as above) the table would be relative small, this would still be pretty quick. Finally, you could implement the method just above but, instead of using bits, you could use each character position to hold Y or N. This creates a table with 1950 records and a ProductFlags column of length 256, but could be entirely implemented in R:Base (using SGET). Hope something here stimulates your imagination! -- Larry
