Help me out of this circular reference!

[#1]

Hello! This is my first question to the community, though I have lurked for some time and found many great resolutions via both the questions and the blogs.

I'm having an issue with a circular reference that I am unable to solve completely, even after going through the blog on the matter (which was enlightening). I have three tables, below. Two are raw, non-configurable files (I cannot change their output). One serves as a simple relationship between partner and price list. They are as such:

Partner Table (manual)	Price List Table (generated)	Exception Table (generated)
Partner Code	Price List	Part Number
Price List	Part Number	Partner Code
Discount	List Price	Quantity

The ask - In the exception file there is a unique line ID for each row. The row will contain a Part Number, a Partner Code, and a quantity. From this, I need to associate the price list the partner uses, then use that list to find the appropriate list price, and multiply through with the appropriate discount. It looks like this:

[Quantity] * [List Price] * (1 - [Discount]) = [Extended Value] for each row. I use variations of this calculation to Sum or Average in aggregate.

Each Part Number has up to four prices (one for each price list), so the Price List Table consists of duplicate part numbers in the Part Number column. Each Partner Code is associated with only one Price List in the Partner Table. From what I understand, I am using Price List in two ways - one is the Price List the Partner Code uses, the other is the Price List the List Price and Part Number are associated with. This is where I am unable to solve my issue.

Somehow, I need each unique row in the Exceptions Table to connect the Partner Code to the correct Price List to the correct List Price for the Part Number on that row. It sounds so simple I have to be missing something right in front of me. Thanks in advance for any help on this!

[swuehl]

I think you are almost there.

I probably would use a JOIN or  Applymap()s to get price list and discount into the exception table, using partner code as key. Then you have two fields that link exception and price list table, and as far as I understood, that is what you need.

You can create a combined key out of the two fields or just leave the synthetic key.

Hope this helps,

Stefan

[#3]

Hey Stefan,

Thanks for the prompt reply.

I have been looking into those as a possibility - do you have an example load statement based on the tables above I could leverage (prior to me poking around in the dark)?

[swuehl]

A basic approch using a JOIN:

[Price List Table]:

LOAD [Price List], [Part Number], [List Price]

FROM YourPriceListTableSource; // you need to put the correct source file name here or use a SELECT on your DB.

[Exception Table]:

LOAD [Part Number],[Partner Code],[Quantity]

FROM YourExceptionTableSource;

LEFT JOIN ([Exception Table])

LOAD [Partner Code],[Price List],[Discount]

FROM YourPartnerTableSource;

This should create two tables, linked by two fields and creating a synthetic key (which is what we need here (or a new key created from the two fields). You should already be able to run your analysis using this data model.

[#5]

Thanks again. I think this approach will work, however I keep getting <Partner Code> not found, so I have to solve for that. I have checked, rechecked, and tripple checked all of my column headers and names and everything and I just cannot get it to recognize Partner Code. I'll be sure to post back once I've moved beyond that issue, and flag this if it indeed solved my issue.

[swuehl]

Where do you get this error message? In your load script? Could you post the script or the document log?

[#7]

I was receiving the error in the load script, but it's likely that something else unrelated is causing me issues as I am very new to this. I've attached very simple samples of the files. If I can get these to work in a load script and show a table that shows 1 transaction id, with 1 part number, 1 partner code, 1 (accurate) price list, 1 (accurate) list price, and discount, then I would call this a success and I can move forward.

What I've generated generally has many rows with duplicate transaction Ids, which is an instant failure.

Thanks again for your help here!

[swuehl · Accepted solution]

Please check if this is correct

[#9]

Thank you very much, this is perfect!

Best,

Russ