topic Hierarchy in QlikView

Hierarchy

sagarkharpude — Wed, 09 Mar 2016 07:10:02 GMT

Hi All,

I want to create hierarchy for the below data:

Item Category	Cost
Z002	195,439
Z003	-
Z003	21,025
Z003	42,049
Z003	38,286
Z003	21,025
Z003	32,445
Z003	-
Z003	24,529
Z003	16,080

Z002 is the sum of all Z003 categories. So by default it will show the sum of Z002. But when i expand the Z002 it will give me the Z003 category wise Cost. Output should be like below:

Default chart:

Item Category Cost

Z002 195,439

When i expand the above Z002 Category output will be(Pivot table)

Item Category	Item Sub Category	Cost	Total
Z002	Z003	-	195,439
	Z003	21,025
	Z003	42,049
	Z003	38,286
	Z003	21,025
	Z003	32,445
	Z003	-
	Z003	24,529
	Z003	16,080

Thanks in Advance

Re: Hierarchy

settu_periasamy — Wed, 09 Mar 2016 07:35:47 GMT

Hi sagar,

How do you identify which one is sub-categroy and which one is Total? is there any logic? Can you provide more sample with different category and sub-category?

Re: Hierarchy

jagan — Wed, 09 Mar 2016 07:46:00 GMT

Hi,

You have a maintain a hierarchy table like below

Data:

LOAD

INLINE [

Item Category, Item Sub Category

Z002, Z003

];

Now in chart (Pivot table) use Item Category, Item Sub Category as dimensions and use Sum(Cost) as expression and enable Sub Totals in presentation tab.

Regards,

Jagan.

Re: Hierarchy

sagarkharpude — Wed, 09 Mar 2016 08:00:11 GMT

@Settu:

User gives the details about which one is Category and which one is sub category. For Category there is total of all the sub category.

Re: Hierarchy

jagan — Wed, 09 Mar 2016 08:11:54 GMT

Can you attach some sample data you are getting? So that it would be easier to provide solution.

Regards,

jagan.

Re: Hierarchy

sagarkharpude — Wed, 09 Mar 2016 08:18:48 GMT

Hi,

Sample is attached for your info. PFA.

Re: Hierarchy

jagan — Wed, 09 Mar 2016 08:36:50 GMT

Hi,

In your sample data there is no flag which tells what it category and sub category. What is the logic to find out that.

Regards,

Jagan.

Re: Hierarchy

sagarkharpude — Wed, 09 Mar 2016 08:39:45 GMT

User has told that Z002 is category and Z003 is sub category.

Re: Hierarchy

avinashelite — Wed, 09 Mar 2016 09:23:43 GMT

In this case, manually you need to create a hierarchy table or data relation and as per the user inputs you need to change the same.

Temp:

Hierarchy (Child, Parent, Child, ParentName, PathSource,

PathName, '-', [Depth])

LOAD * inline

[

Parent,Child

Z002,Z003

];

Re: Hierarchy

settu_periasamy — Wed, 09 Mar 2016 10:10:21 GMT

Hi,

May be like this?

Directory;
T1:
LOAD [Item Category],
Cost
FROM
[sample (4).xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(T1)
LOAD [Item Category],Sum(Cost) as New Resident T1 Group by [Item Category] Order by [Item Category];

NoConcatenate
Final:
LOAD RecNo() as Rec,
if(Cost=New,[Item Category],Peek([Item Category])) as [Item Category],
if(Cost<>New,[Item Category]) as subcategory,
if(Cost=New,'',Cost) as Cost,
if(Cost=New,New) as Total_Val Resident T1 where Cost>0 Order by [Item Category] ;
DROP Table T1;