Karl Chalabala Posted January 24, 2019 Share Posted January 24, 2019 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 Link to comment Share on other sites More sharing options...
James Watts Posted January 25, 2019 Share Posted January 25, 2019 If you have many receipts and many deliveries, how do you match receipts to deliveries Link to comment Share on other sites More sharing options...
Karl Chalabala Posted January 25, 2019 Author Share Posted January 25, 2019 They are segregated by individual contracts and time. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted January 29, 2019 Share Posted January 29, 2019 can you add the time and contract column, and upload a sample dataset that reflects the types of relationships Link to comment Share on other sites More sharing options...
James Watts Posted January 29, 2019 Share Posted January 29, 2019 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 Link to comment Share on other sites More sharing options...
Karl Chalabala Posted January 29, 2019 Author Share Posted January 29, 2019 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 More sharing options...
Gaia Paolini Posted January 29, 2019 Share Posted January 29, 2019 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 More sharing options...
Karl Chalabala Posted January 29, 2019 Author Share Posted January 29, 2019 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. Link to comment Share on other sites More sharing options...
James Watts Posted February 21, 2019 Share Posted February 21, 2019 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now