Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Value distribution from children to parents

Hi guys,

I have a master data table with a hierarchy of sellers (Code, Superior, Name) and a transaction table with the sales (Code, Sale, Amount, Date).

What I wanted to figure out is how to add a field GroupAmount for each seller which would contain the sum of all the sales of the seller's children in the hierarchy.

For example, there are 3 sellers: Bob, Mary, Mario.

Bob is the group's superior, so hierarchically Bob is parent to Mary and Mario.

If Mary and Mario sell 100 and 200 items in the month, Bob needs a related field GroupAmount equal to 300 created. The field's value for Mary and Mario remains 0 since they don't have any children in the hierarchy.

How can I do that? 🙂

Thanks.

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Valera,

the first question is - why? Why do you need to pre-summarize data and not use QlikView aggregation to do the job for you?

To answer your specific question - you'd need to add the "parent" codes to the transactional table and then aggregate the data using a resident load and a "GROUP BY". For example:


left join (Transactions) load
Code, Superior
resident Hierarchy;
Summary:
load
Superior,
sum(Sales) as SuperiorSales
resident Transactions
Group By Superior
;


This, of course, only covers one level of parent/child hierarchy. If you have more than that, you'll have to flatten it first.

View solution in original post

3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Valera,

the first question is - why? Why do you need to pre-summarize data and not use QlikView aggregation to do the job for you?

To answer your specific question - you'd need to add the "parent" codes to the transactional table and then aggregate the data using a resident load and a "GROUP BY". For example:


left join (Transactions) load
Code, Superior
resident Hierarchy;
Summary:
load
Superior,
sum(Sales) as SuperiorSales
resident Transactions
Group By Superior
;


This, of course, only covers one level of parent/child hierarchy. If you have more than that, you'll have to flatten it first.

Anonymous
Not applicable
Author

Oleg,

thanks for your reply. 🙂

I wanted to pre-calculate all the data while loading to freely build reports. Those "group sales" values will then be used to make further calculations. Therefore, I'm not sure if I could manage all this directly in the charts.

I'm new to Qlikview, so, need some more experience.

Your answer inspired me on finding the solution. I wanted to ask if this was the optimal solution or it could be done somehow better.

So, again, the objective is to calculate the group sales of the children per parent.

Step 1: hierarchy creation



TempTab:
LOAD ??? as TempAdvisorCode,
??? as TempAdvisorName,
[??? ????????] as TempSponsorCode
FROM

(ooxml, embedded labels, header is 1 lines, table is ?????????)
WHERE(not IsNull(???));

LR:
Hierarchy ([Code], [SponsorCode], [Advisor Name], Sponsor, [Code], Path, '-')
LOAD
[TempAdvisorCode] as [Code],
[TempSponsorCode] as [SponsorCode],
[TempAdvisorName] as [Advisor Name]
RESIDENT TempTab;

LRAllSuperiors:
HierarchyBelongsTo ([Code], [SponsorCode], [Name], [SuperiorCode], Superior)
LOAD
[TempAdvisorCode] as [Code],
[TempSponsorCode] as [SponsorCode],
[TempAdvisorName] as [Name]
RESIDENT TempTab;

Levels:
LOAD
[Code],
count([Code]) as Level
RESIDENT LRAllSuperiors
GROUP BY
;

drop table TempTab;

Step 2: loading transaction data

Sales:
LOAD ??? as Code,
sum (??) as SP
FROM

(ooxml, embedded labels, header is 1 lines, table is ???????)
group by ???;
INNER JOIN (Sales)
load
Code
,SuperiorCode as SponsorCode
resident LRAllSuperiors;

GroupSales:
load
SponsorCode as Code
,sum (SP) as GroupSP
resident Sales
where SponsorCode <> Code
group by SponsorCode;

drop field SponsorCode from Sales;


Is this solution for finding the aggregated values acceptable?

Thanks

vidyut
Partner - Creator II
Partner - Creator II

Hi,

In effect, you are using the HierarchyBelongsTo to get the hierarchy, and then grouping all the sales for children. this is a good use of the recently introduced hierarchy functions and quite a acceptable way.