Jump to content
We've recently updated our Privacy Statement, available here. ×

Allocating mixed volumes in different columns

Karl Chalabala

Recommended Posts

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.



Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • 4 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...