8 Replies Latest reply: Aug 28, 2012 1:58 PM by Kent Shook RSS

    How would you load this?

    Kent Shook

      I have an interesting problem and I'm looking for new ways around it.

       

      Basically, I have a bunch of invoices and the items thereon being given to me in large, flat excel files.

       

      To accomplish the desired mission, I need to be able to categorize all of the items. There are 52 "Operations" which are high-level descriptions of what's being done, and there are over 30,000 other items that give the details. I have an Excel file that has categories for all of the operations.

       

      I would like to categorize the rest of the items automatically. My idea for how to do this is to look for invoices that have only a single Operation item, and assign that item's category to the rest of the items on that invoice. I have a bit over 250,000 invoices, and 45,000 of them have only one Operation item. I'm creating a table of all of the invoices that have a single operation, and assigning that operation's category to all the items.

       

      Here's where I run into problems: Those items *also* appear on invoices that have multiple Operation codes, where I can't distinguish the category. Because of that, the items show up in the data twice, once with the correct category, and once with a category of null.

       

      How can I get a distinct list of item numbers with the categories and not have those nulls? Is there a better way to categorize the items than the approach I'm taking?

       

      Thanks!

        • Re: How would you load this?

          It's hard to decifer this without seeing it, but could you assign a flag, something like, if invoice operation = only 1 operation, set flag to 1 else 2.  Then based on that flag assign the category?

            • Re: How would you load this?
              Kent Shook

              I'm not having a problem assigning the categories to the invoices that only have a single operation - The problem is that the same items will still have a null category if they're on an invoice that doesn't have a single operation.

               

              What I'm looking for is a way to eliminate the duplicates, or avoid them in the first place.

                • Re: How would you load this?

                  Trying to understand this, if you have Item1 and Item1 can show up on many invoices.  However, Item1 can show up on an invoice with only 1 operation and it can show up on an invoice with several operations.  Is this correct?  Also, are you saying that Item1 can have its own assigned category and it could also be assigned another items category if it shows up on an invoice with other items that only have 1 operation?

                    • Re: How would you load this?
                      Kent Shook

                      Okay, here's an example:

                       

                      Sales data:

                      Invoice #     Item #

                      1               Operation-Foo

                      1               Part-F

                      1               Part-o

                      2               Operation-Foo

                      2               Operation-Bar

                      2               Part-F

                      2               Part-o

                      2               Part-B

                      2               Part-a

                      2               Part-r

                       

                      Category data:

                      Item#               Category

                      Operation-Foo     Gadgets

                      Operation-Bar     Gizmos

                       

                      What I'd like to happen is to end up with an Items table as follows:

                      Item #               Category

                      Operation-Foo     Gadgets

                      Operation-Bar     Gizmos

                      Part-F               Gadgets

                      Part-o               Gadgets

                      Part-B               Gizmos

                      Part-a               Gizmos

                      Part-r               Gizmos

                       

                      The way I'm doing things right now, the last three would not be assigned the category of "Gizmos" which is something I'd like to do, but that's beyond what I'm trying to get.


                      What I'm getting right now is:

                      Item #               Category

                      Operation-Foo     Gadgets

                      Operation-Bar     Gizmos

                      Part-F               Gadgets

                      Part-F               -

                      Part-o               Gadgets

                      Part-o               -

                      Part-o               Gadgets

                      Part-B               -

                      Part-a               -

                      Part-r               -

                       

                      Hope this makes sense... Thanks!