How would you load this?

[Anonymous]

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!

[#2]

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?

[Anonymous]

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.

[#4]

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?

[Anonymous]

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!

[#6 · Accepted solution]

By taking all of the invoices that have a single operation you are assigning the items a category (lets call it table 1)  are you then trying to use  table 1 as a look up table to assign the category to all the items on the invoices with multiple operations?  If this is it, you could use an apply map.  Let me know if I am getting it or not. 🙂

[Anonymous]

Well, I solved it. I built a table with all of the distinct items on the single-operation invoices, applied the single-operation category, and then used that as a lookup table when loading all the items.

A mapping table might work, too... In fact, I may try that, as the lookup was pretty slow.

[#8]

Excellent, good luck.

[Anonymous]

One more thing: Only about 1/3 of the items got categorized based on the single-operation invoice trick - Only slightly under 20% of the invoices have a single operation on them. I'm trying to come up with a way of categorizing the rest of the items... Any ideas would be appreciated!