Combine Parent Child values in chart

[markaddax]

Hi...hopefully a quick and easy solution.

In a straight table, I'm displaying a summary view of parent jobs. Each parent job however has 3 child jobs and I need to include the child job values as a sum total on the parent job row.  The child jobs are not to be displayed. The dimension is the parent record. As an example...

• Parent record (code: 1000, type: Installation job) - value $5,000
• Child record (code: 1000.1, type: Warranty job) - value $300
• Child record (code: 1000.2, type: Rectification job) - value $600
• Child record (code: 1000.3, type: Variation job) - value $100

The invoice total for this row in my chart I'd want to see is $6,000. 

The table back-end is a Transaction table containing all the job invoices and another Job table containing all the Job masterfiles where I also do a Lookup in the load script to find the records parent (storing it as [Job Parent]). The construct of the parent/child jobs above doesn't change across thousands of jobs. Every parent 'Installation' job will have its own 3 'Warranty, Rectification and Variation' child jobs. 

I've tried numerous methods using Set Analysis and Aggr but I'm missing something fundamental I feel. 

Any help appreciated. Thanks

[edwin · Accepted solution]

this may be a modeling exercise.
assuming your transaction file has CODE field that stores both parent and child code, you just need a lookup table that has the parent child relationship,  this lookup must also have the parent code (see comment)

the chart will just be a simple dim=Parent, exp=sum(values)  nothing fancy

load * inline [
code,type,value
1000, Installation job,5000
1000.1, Warranty job,300
1000.2, Rectification job,600
1000.3, Variation job,100
];

load * inline [
parent, code
1000, 1000      //this is the parent code 
                 //so that the parent links back to the transaction file
1000, 1000.1
1000, 1000.2
1000, 1000.3
];

[markaddax]

Hi Edwin, you are indeed correct and I like the solution. Thank you