Hi, I have a question on aggregating the data, and the data structure looks like an example below: (Table A) Background: rental car industry, a customer rent a car from 17JAN2017 to 19JAN2017, then the 'Start_Date' is 17JAN2017; length of use is 3. ID is just a sequence number; Probability is the percentage for each length_of_use falls in each Start_Date, means that how much percentage of the same Start_Date will rent the car for a certain length. The first row means 1/3 of the demand starting from 17JAN2016 will use for 1 day; and 1/3 will use for 2 days; the rest 1/3 will be used for 3 days. ID | Start _Date | Length_of_Use | Probability 1 | 17JAN2016 | 1 | 0.333 2 | 17JAN2016 | 2 | 0.334 3 | 17JAN2016 | 3 | 0.333 4 | 18JAN2016 | 1 | 0.5 5 | 18JAN2016 | 4 | 0.5 6 | 19JAN2016 | 2 | 0.5 7 | 19JAN2016 | 3 | 0.5 8 | 20JAN2016 | 2 | 1 9 | 21JAN2016 | 1 | 1 And I do have Demand by Use date level as below: (Table B) 'Use_Demand' means on that day, how many cars are being used by customers on that 'Use_Date', and it is also known information. ID | Use_Date | Use_Demand 1 | 17JAN2016 | 3 2 | 18JAN2016 | 4 3 | 19JAN2016 | 4 4 | 20JAN2016 | 4 5 | 21JAN2016 | 5 If we have the above 2 tables as known information, how could I merge table A and table B into a table that could have demand by Start Date & Length of Use level as below? We would like to figure out demand by 'Start_Date' & 'Length_of_Use' level (Table C) based on demand by 'Use_Date' level (table B) and Probability for each 'Starting_Date' and 'Length_of_Use'. Which means how Table A and Table B could obtain Table C. More detailed example may help: As the example in Table B, 17JAN2017 has use_demand of 3, and according to the probability, 1 of them has length of use 1, whcih means the 'use_date' is 17JAN2017; 1 of them has length of use 2, whcih means the 'use_date' is 17JAN2017 and 18JAN2017; the rest 1 has length of use 3, whcih means the 'use_date' is 17JAN2017, 18JAN2017 and 19JAN2017. As in table B, 18JAN2017 has use_demand of 4, 2 of them come from 17JAN2017, hence, the rest 2 of them should come from 18JAN2017. Based on the probability table (table A), Starting_Date 18JAN2017 has 2 types of length of stay, both have probability 0.5; hence Start_Date '18JAN2017' with length of use 1 has demand 1, and '18JAN2017' with length of use 4 has demand 1; whcih makes 4th and 5th row of table C. Thank you! ID | Start _Date | Length_of_Use | Demand 1 | 17JAN2016 | 1 | 1 2 | 17JAN2016 | 2 | 1 3 | 17JAN2016 | 3 | 1 4 | 18JAN2016 | 1 | 1 5 | 18JAN2016 | 4 | 1 6 | 19JAN2016 | 2 | 1 7 | 19JAN2016 | 3 | 1 8 | 20JAN2016 | 2 | 1 9 | 21JAN2016 | 1 | 2
... View more
