Total Amount consist of amounts from another tables

[beck_bakytbek]

Hi Folks,

i have a situation: i have 3 Tables: Project, Contract and Order, all these tables have the column: Amount.

for instance:

Project: 9001 has 100, this amount consists of amounts from table: Contract (20, 70, 10) and these amounts consist of amounts: 5, 5, 10, 70, 10 from the table: Order and i want to show, that Project: 9001 consists of 3 ContractNr, and these 3 ContractNrs consist of 5 OrderNrs

As you see all these tables have amounts that flow into amount 100 in the table Project, my question is, how to handle this situation (what is the best way) in script and whithin in Pivot-Table, in Pivot-Table i want to show that 100 consist of small amounts from another tables. (see screenshot).

Thanks a lot

Beck

[ogster1974]

Try and generate a fact table in script that contains

Projectid,

Contractid,

Orderid,

Amount

Then you can create measures that sum at order, contract or project level.

[beck_bakytbek]

Hi Andy,

thanks a lor for your responce, i made thay way but i didn't have the expected results. do you have any idea?

[ogster1974]

Do you have a sample app you can share.  or screen shot what's the issue.   your requirement to roll up values should be handled by the above structure.   do you have duplicate data values effecting the results?

[beck_bakytbek]

Hi Andy. i dont have any duplicate values

[ogster1974]

So can you show us a sample of your data as stored in your app so we can help you further.

[justinphamvn]

Hi Beck,

Try like this:

Script Load:

Project:
LOAD   
*  
INLINE   
[  
Project_ID,Project_Nr,Project_Name,Project_Amount
1,9001,AA,100
2,9002,BB,250
]  
(DELIMITER IS ',');


Contract:
LOAD   
*  
INLINE   
[  
Project_Nr,ContractNr,ContractName,ContractAmount
9001,1_9001,AA1,20
9001,2_9001,AA2,70
9001,3_9001,AA3,10
9002,1_9002,BB1,150
9002,2_9002,BB2,50
9002,3_9002,BB3,50
]  
(DELIMITER IS ',');


Order:
LOAD   
*  
INLINE   
[  
ContractNr,OrderNr,Order_Name,Order_Amount
1_9001,1,Order_1,5
1_9001,1,Order_1,5
1_9001,1,Order_1,10
2_9001,2,Order_2,70
3_9001,3,Order_3,10
1_9002,1,Order_1,75
1_9002,2,Order_2,75
2_9002,2,Order_2,50
3_9003,3,Order_3,10
3_9003,3,Order_3,20
3_9003,3,Order_3,20


]  
(DELIMITER IS ',');

Chart function:

Pivot table

     Dimension: Project_ID, ContractNr, OrderNr

     Measure: sum(Order_Amount)

This is look like below:

Hope this helps

Justin.

tProject_ID

[beck_bakytbek]

Hi Justin,

thanks a lot for your responce. my first procedure does look like your procedure, my issue is: within pivot-table i am not able to see the amounts : 5 +5 = 10

1. 1_9001,1,Order_1,5  , 5
2. 1_9001,1,Order_1,5 ,  5

do you have any idea, how to handle this case?

thanks a lot

[justinphamvn]

Hi Beck,

I think the problem is OrderNr not unique (It's not a Key of Table Order)

You should add a column key for table Order, and used this key instead of OrderNr

Regards,

Justin.

[beck_bakytbek]

Justin, thanks a lot for your feedback,

if i could have done the OrderNr as unique. how does look the handling in this case?