Sum of child values

[engishfaque]

Dear All,

I'm going to add child values based on parent and trying to hide Parent, here is the input and required output.

Input

Parent	Child	Values
NI-R6501-Gadsden35-MF-006-Set3		1
NI-R6501-Gadsden35-MF-006-Set3	NI-gadsden35	5
NI-R6501-Gadsden35-MF-006-Set3	NI-MF-006	2
NI-R6501-Gadsden35-MF-006-Set3	NI-R6501-ZZ8	1

Required Output: Parent value = 1, then add 1 in each child values such as given below table. As well as only shows child not parent.

NI-gadsden35	6
NI-MF-006	3
NI-R6501-ZZ8	2

Kind regards,

Ishfaque Ahmed

[stigchel]

If I understood you correctly something like

Sum(Values)+Aggr(Sum(TOTAL <Parent> if(len(Child)=0,Values)),Parent,Child)

Also see attached

[engishfaque]

Dear Piet,

I need to convert this logic into qlikview,

Sum(child values + parent value) where childs belongs to their own parent.

Kind regards,

Ishfaque Ahmed

[engishfaque]

Dear Piet,

Is this possible we use, p() in expression to achieve required output?

Kind regards,

Ishfaque Ahmed

[Anonymous]

If you have a field that indentifies Parent/Child, for example Type, you can have a straigth table with

-first dimension (calculated): if(Type='Parent', Parent) with option Suppress When Value is null and where Parent is the field shown in your table above;

-second dimension: Child;

-expression: sum(value) + sum({<Type={'Parent'}>} value).

It seems to work,

let me know.

BR

Elena

[engishfaque]

Dear Elena,

I made expression, my expression is working when I'm going to select particular parent child set.

But issue is coming when I'm going to press clear button.

For example:

27 records are there with having type = parent, when I press button clear these parent values are going to be added and effecting to others.

Kind regards,

Ishfaque Ahmed

[MarcoWedel]

Hi,

one solution in the script could be:

table1:
Hierarchy(ChildID, ParentID, Child,,Values ,'Total Values','+','Depth')
LOAD *,
    AutoNumber(Child) as ChildID,
    AutoNumber(Parent) as ParentID;
LOAD Parent,
    If(Len(Child),Child,Parent) as Child,
    Values
FROM [http://community.qlik.com/thread/150075] (html, codepage is 1252, embedded labels, table is @1);

Right Join (table1)
LOAD Child,
    [Total Values],
    Evaluate([Total Values]) as [Total Value]
Resident table1
Where Depth=2;

DROP Field [Total Values], ChildID, ParentID;

hope this helps

regards

Marco

[sujeetsingh]

You can go with the input field in your application.

That means what ever be the value of Parent, the value gets added in the child details.