Dynamically Generate a Table from Information Distributed Across Data Structure

Question

&nbsp;I have multiple tables in my data structure.I need to auto generate a table that allocates volumes from the tblCtrMtrDetail table to the individual price indices to which the volumes have price exposure. The price indices to which each tblCtrMtrDetail record’s volume is exposed are determined from the price schedule associated with the contract and identified in the tblCtrAttributes file.&nbsp; Each price schedule is a weighted average of various indices.&nbsp; The weight for each index used in a price schedule is in tblSchedIndexDetail for which the primary key is Sched_id &amp; index_id.The generated table would ideally contain the following fields:Ctr_Id, Mtr_Id, Prod Date, Price Schedule Id, Index Id, Index Proportion, Allocated Volume (calculated field)

anonymous · Answer

Hi&nbsp;TonySwad,&nbsp;You could create a table from Modelling tab using Distinct(tblCtrMtrDetai[Ctr_Id]), then using Lookup pull in all other columns.&nbsp;&nbsp;Does this help?

tonyswad · Answer

Thank you for your response.&nbsp; That helped me with another problem.&nbsp; Unfortunately,&nbsp; I&nbsp;do not think that works for my&nbsp;posted problem.&nbsp; Let me explain.&nbsp;&nbsp;For each unique Ctr_Id in tblCtrMtrDetail, I need as many rows as their are indices associated with the price schedule associated with the Ctr_Id.&nbsp; For example, for Ctr_Id =1 and Mtr_Id = 1, I have volume 100.&nbsp; Ctr_Id =1 has an associated Price Schedule Id =1.&nbsp; Price Schedule Id = 1, has 3 price indices associated with it Index_Id ={1,2,3} with Index_Proportions of {25%,25%,50%}&nbsp;for index 1,2,and 3 respectively.&nbsp; For the given CtrMtr combination (1,1), I would need 3 rows in my auto-generated table.&nbsp;for that&nbsp;scenario&nbsp;the three output table rows would need to be:Ctr Id&nbsp;&nbsp; Mtr Id&nbsp;&nbsp; Sched_Id&nbsp;&nbsp; Index_Id&nbsp;&nbsp; Allocated Volume1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 251&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 251&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50

anonymous · Answer

TonySwad,Could you please share sample data of your tables?Regards,Lydia

tonyswad · Answer

The following are samples of the data in the tables relevant to my posted problem.The following table relates to a contract entity and is used to relate a schedule to each contract.Ctr_Id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Co_Id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cust_Id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sched_Id111122223333414452516312712382349341101521121312324131311424215353161141722118332191432025421311&nbsp;The following table is the detail for the rate schedules.&nbsp; Each rate schedule is the weighted average of one or more indices.&nbsp; The table contains a row for each index used in a given rate schedule and the weighting applied to the index in that given rate schedule.&nbsp;Sched_Id&nbsp; Index_Id&nbsp; Index_Proportion110.25120.25130.25140.25210.5220.5310.333333333320.333333333330.333333333411&nbsp;This table contains metered volumes assigned to a contract in a given month.Ctr_Id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Mtr_Id&nbsp;&nbsp;&nbsp; Volume&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Prod Date19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 5,81012/1/2017119&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,08112/1/2017120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 2,38812/1/201722&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,98512/1/2017234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 48812/1/2017239&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,47012/1/2017311&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 4,94612/1/2017425&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (7,056)12/1/2017440&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 5,68712/1/201754&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,32512/1/2017616&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 8,88312/1/2017618&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (9,178)12/1/2017624&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (245)12/1/2017631&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6,818)12/1/2017637&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (2,811)12/1/2017717&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (2,447)12/1/2017730&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 2,28812/1/2017827&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,51612/1/2017914&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 7,99512/1/2017921&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,01112/1/2017929&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 6,18412/1/20171026&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,25512/1/2017117&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (7,500)12/1/20171133&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4,271)12/1/20171210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 4,02912/1/2017135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (1,131)12/1/20171328&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (877)12/1/2017141&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (258)12/1/20171436&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (844)12/1/20171438&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (6,577)12/1/20171512&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (4,918)12/1/2017163&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,98212/1/20171715&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 5,15312/1/20171735&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (9,369)12/1/2017186&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (1,346)12/1/2017198&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (917)12/1/20171913&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 9,83812/1/20172022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4,111)12/1/20172032&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (9,260)12/1/20172123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (1,758)12/1/2017The problem is to allocate the metered volumes per contract-meter-month record to the individual indices to which the metered volume is exposed.&nbsp; I was hopeful of creating a table with the following headings:&nbsp;Ctr_Id&nbsp; Mtr_Id&nbsp; Prod_Mo Sched_Id&nbsp; Index_Id&nbsp; &nbsp;Volume&nbsp;such that when I added the volume for a given Ctr_Id Mtr_Id Prod_Mo across Index_Id's, the total would equal the volume for the Ctr_Id Mtr_Id Prod_Mo record in the 3rd table above.&nbsp;By generating such a table, I can write one set of measures on that table that allows me to report at the level of detail I require.

Forum Discussion

Dynamically Generate a Table from Information Distributed Across Data Structure

4 Replies

Related content

PQ -Date Distribution

Dynamic bins for price distribution

Distribution Chart

Rest API Informations

Normal Distribution