Allocating mixed volumes in different columns

[Karl Chalabala]

Hi - Does anyone happen to know how allocate volumes into a combined column Examples are simple version, there could many-to-few, one-one or many-to-many relationships. Thank you, Karl

[James Watts]

If you have many receipts and many deliveries, how do you match receipts to deliveries

[Karl Chalabala]

They are segregated by individual contracts and time.

[Gaia Paolini]

can you add the time and contract column, and upload a sample dataset that reflects the types of relationships

[James Watts]

What are the business rules for the allocation  E.g if you had three receipts (4, 6, 10) and three deliveries (5, 7 and 8), how would you match them up  

[Karl Chalabala]

I added the data set as it comes into our server (example.xlsx).

 

This data informs a gas transportation model on pipelines between receipt points and delivery points (as reported from the pipeline's perspective, i.e. gas is received from the pipeline at one flow point than delivered to a delivery point). 

 

The key data segregations are "contract number" (Column D), "effective data" and "expiration date" of the contracts (Columns G & H), "Daily Transportation Qty Dth," (column I) which equals total contract gas quantity to be delivered by contract (repeated for each row of respective contract), Flow Point Name (column O) or "Flow Point Number" (column W), which are discrete, "Flow Direction" - (column T), which determines which flowpoints are the Receipt or Delivery points for the contract, then "Flow Point Quantity Dth," (Column U), the gas quantity for each flow point).

 

To Jim's question, the allocation of gas by delivery point is most important, so if there are 4 receipt Flow Points with different volumes into 2 Delivery Flow Points, do not need to get the actual flows, which is another data set.  Just need to create an allocation that says this amount of these 4 flowpoints (up to max flowpoint delivery in the contract) goes to the two delivery points.

 

So if one reciept flowpoint filled all the first delivery flow points, the remaining three would go to the second delivery flowpoint.

 

The issue arises when you have remainders, I.e. 1 flowpoint fills the first delivery point and has left over volumes to allocate to delivery point 2, or vice versa.

 

The output columns would be new columns with new Receipt Columns of all the data matched to new Delivery Columns of all the data, a function of the "Flow Direction" , with rows determined by the calculated Flow Point Quantity. I know how to create all the new columns, etc, it's calculating the Flow Point quantity for mixed receipts and deliveries that is what I'm solving for.

 

I'm currently learning R to help with this as well, as this is likely beyond Spotfires capabilities due to row generation needs.

 

 

[Gaia Paolini]

Thanks. I understand the Flow Point quantity Dth plays the Volume here. However, delivery and receipt are still in unspecified order within each contract, as the Effective Date and the Expiration Date are the same within each contract. In contracts with multiple records, volumes of Receipt and Delivery appear in different orders and/ordo not match up. I am afraid I don't have enough business knowledge to understand what you need.

[Karl Chalabala]

Thank you Gaia.  I appreciate you taking the time to at least look at it.  It's complicated, but easily done in excel by company,but automating it for scale is proving to be a very difficult.  Will keep at it.

[James Watts]

I think using a join might allow you to create the necessary rows if you split the data into two tables for receipts and deliveries.  However, the business logic is still unclear to me.  Could you share the s/sht that works, or capture the calculation you want performed please