topic Re: Need help for grouping data. in QlikView

Need help for grouping data.

agni_gold — Sat, 09 May 2015 08:09:00 GMT

I am attaching the data in excel , and i want result like attached screenshot.

I have tried many ways but not able to do this.

Please help

Re: Need help for grouping data.

MarcoWedel — Sat, 09 May 2015 08:57:50 GMT

Should work with a pivot table out of the box.

Re: Need help for grouping data.

Gysbert_Wassenaar — Sat, 09 May 2015 09:07:16 GMT

A single dimension column in a pivot table that expands expands like a treeview listbox is not possible in Qlikview.

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 09:18:35 GMT

is any other way to do this ?

Or what should be the data structure (in attached excel) to do this.

Re: Need help for grouping data.

Gysbert_Wassenaar — Sat, 09 May 2015 09:21:07 GMT

Simply use a standard pivot table with all the levels of the hierarchy as dimensions. Expand the dimensions as needed.

Or code an alternative pivot table object as an extension object.

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 09:43:46 GMT

other than pivot table , is any other chart object can do this ? like straight table

Re: Need help for grouping data.

Gysbert_Wassenaar — Sat, 09 May 2015 10:04:19 GMT

No.

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 11:07:56 GMT

This type of grouping is possible in qliksense.

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 11:18:16 GMT

Actually i saw something here

Unbalanced, n-level hierarchies

but how can i do that , i dont know.

Re: Need help for grouping data.

sunny_talwar — Sat, 09 May 2015 11:52:18 GMT

There is a supporting document with the blog you shared which gives more insights on when it can be used and kind of how it can be implemented. Have you looked at that?

Hierarchies‌

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 12:03:15 GMT

But how can i implement , i dont know

Re: Need help for grouping data.

Sat, 09 May 2015 13:38:23 GMT

Hello Agnivesh,

I have looked at the the data file you attached. It does not reflect the country breakdown structure. Any how, base don the file you posted, I built a hierarchy. Please have a look and I hope it helps and apologies if it doesnt. If your file was structured differently, you could use the transformation tool in qlikview which will allow you to easily build the hierarchy. You might need to modify the query in case of ull values to get the correct node.

Re: Need help for grouping data.

Anonymous — Sat, 09 May 2015 14:06:17 GMT

Does this give you food for thought ?

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 14:45:48 GMT

Sorry friend i have only personal edition , so i am not able to open this file , but yes it will give me food can you please share the qvs file of this . So that i can see .

Thanks

Re: Need help for grouping data.

Anonymous — Sat, 09 May 2015 14:51:11 GMT

Data:

load

1 as Count ,

if ( not isnull( [Child Description4] ) ,

[Parent Description] & '/' & [Child Description1] & '/' & [Child Description2] & '/' & [Child Description3] & '/' & [Child Description4] ,

if ( not isnull( [Child Description3] ) ,

[Parent Description] & '/' & [Child Description1] & '/' & [Child Description2] & '/' & [Child Description3] ,

if ( not isnull( [Child Description2] ) ,

[Parent Description] & '/' & [Child Description1] & '/' & [Child Description2] ,

if ( not isnull( [Child Description1] ) ,

[Parent Description] & '/' & [Child Description1] ,

if ( not isnull( [Parent Description] ) ,

[Parent Description] ) ) ) ) )

as Tree ,

;

LOAD [HFM Account Parent],

[Parent Description],

Child1,

[Child Description1],

Child2,

[Child Description2],

Child3,

[Child Description3],

Child4,

[Child Description4]

FROM

(ooxml, embedded labels, table is Hirerchy);

Then you can make a List Box Object on Tree and on the General tab <tick> Show as Tree View.

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 15:03:33 GMT

Thanks friend , but this will not work on pivot table or any table type object . This can only be used as individual field. Am i right

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 15:09:58 GMT

It comes like this

But i want Tree column should be like this

When i drill down it should be in Tree column and expand as per hierarchy.

Re: Need help for grouping data.

Anonymous — Sat, 09 May 2015 16:28:26 GMT

Have you tried creating a List Box Object on field Tree and then on the General tab <tick> Show as Tree View. It should look just like your example of what you are after.

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 16:32:42 GMT

Yes i have tried , but what exactly i want is , I want to make the Pivot table in which Hierarchy should be in single column . like this

Re: Need help for grouping data.

agni_gold — Sat, 09 May 2015 16:37:58 GMT

i got this image in one of the blog

https://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies