This took me some time to finish. But all in all it was a fun challenge.
STEP: 1. Parsed the soil composition in the borrow file and then used a transpose tool to get all the soil types in one column. 2. After the transpose - the data was then parsed again to get the % composition of all the soil types.Also, additionally I have cleaned the data using the cleansing tool as there were leading spaces. 3. In the next step I calculated the room volume and also the %cubic value for each soil for a room. 4. In the next step I calculated the weight of the soil for each room. 5. In the next couple of step , I calculated the total weight of the soil for each borrow and then calculated the average weight of the soil for all borrows. 6. In the next step I calculated the average weight of the wood per cubic foot for all the wood types and appended it to the average soil weight we got form the previous step. 7 In the next step I calculated the Wood chucked if a woodchuck could chuck wood.
My numbers didn't match with the expected out at first as I didnt convert the (l,W,h) values to foot at first. Also all my calculated fields were integers. I had to convert them to 'Double' to get the exact result.