Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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
];
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
];
Hi Edwin, you are indeed correct and I like the solution. Thank you