topic Re: Combine Parent Child values in chart in QlikView

Combine Parent Child values in chart

markaddax — Fri, 15 Nov 2024 23:54:10 GMT

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

Re: Combine Parent Child values in chart

edwin — Tue, 29 Sep 2020 17:07:46 GMT

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 ];

Re: Combine Parent Child values in chart

markaddax — Wed, 30 Sep 2020 07:00:36 GMT

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