topic Re: Unable to create hierarchy in PivotTable in QlikView

Unable to create hierarchy in PivotTable

Thu, 02 Apr 2015 13:45:50 GMT

This is how my dataset looks like:

ItemType ItemCategory ItemName

ItemType1 ItemCat1 Item1

ItemType1 ItemCat1 Item2

ItemType1 ItemCat2 Item3

ItemType2 ItemCat1 Item4

ItemType2 ItemCat2 Item5

ItemType2 ItemCat2 Item6

ItemType3 ItemCat1 Item7

ItemType3 ItemCat2 Item8

ItemType3 ItemCat1 Item9

I am trying to display this in PivotTable so that i can get tree view kind of structure with ItemCategory as Row rather than column.

ItemType ItemCat1 ItemCat2

-ItemType1 Item1 Item3

Item2

-ItemType2 Item4 Item5

Item6

-ItemType3 Item7 Item9

Item8

To achieve this, this is what i did.

I created ItemType and ItemCategory as Dimensions and ItemName as Expression. But it does not show me the data in the ItemName column.

I created ItemType, ItemCategory and ItemName as Dimensions, but it does not let me drag and drop ItemCategory column on top of ItemName. It however shows the data.

I created ItemType, ItemCategory and ItemName as Dimensions and ItemName as Expression, it allows me to drag and drop the ItemCategory on top of ItemName expression column but the result is not what i am looking for.

What am i missing here?

Re: Unable to create hierarchy in PivotTable

Thu, 02 Apr 2015 17:46:49 GMT

You could try loading it as a Hierarchy to get list box tree view. Check out this document Hierarchies

by hic‌

Re: Unable to create hierarchy in PivotTable

kamal_sanguri — Thu, 02 Apr 2015 18:15:24 GMT

what did u use in expression.. try using -- ONLY(ItemName). aS expression.

Thanks,

Kamal

Re: Unable to create hierarchy in PivotTable

Anonymous — Thu, 02 Apr 2015 18:29:14 GMT

You have to use all three as dimensions.

Re: Unable to create hierarchy in PivotTable

kamal_sanguri — Thu, 02 Apr 2015 18:34:13 GMT

OK, JUST LOOK AT THE ATTACHED FILE...

YOU HAVE TO CREATE A PIVOT FIRST AND THEN YOU CAN HOLD AND DRAG A DIMENSION AND PUT IT ABOVE THE EXPRESSION.

IN ATTACHED FILE YOU WILL SEE TWO PIVOTS CREATED WITH SAME DATA ( DIMENSION AND EXPRESSION) BUT THEY LOOK DIFFERENTLY.

Re: Unable to create hierarchy in PivotTable

Anonymous — Thu, 02 Apr 2015 19:07:04 GMT

All three must be in dimensions to get the hierarchy view

Re: Unable to create hierarchy in PivotTable

petter — Thu, 02 Apr 2015 19:36:07 GMT

Is this what you are looking for? It has some limitations as the number of items has to be few and fixed like

you have in your example maximum 2 or 3 ....

Re: Unable to create hierarchy in PivotTable

Thu, 02 Apr 2015 20:06:51 GMT

This is very very close. I do not want to concat the item names. I want them to show up in rows. But rest is exactly what i am looking for.

Re: Unable to create hierarchy in PivotTable

Thu, 02 Apr 2015 20:16:01 GMT

I want ItemCategory to show up as Columns rather than rows.

Re: Unable to create hierarchy in PivotTable

petter — Thu, 02 Apr 2015 20:18:16 GMT

I understand - but that demands much more manipulation of the data model ... I think...

Re: Unable to create hierarchy in PivotTable

petter — Thu, 02 Apr 2015 20:19:08 GMT

ItemCategory has been pivoted out as a Column... it is not a row if you look closely .. you can remove or minimize the "label" column if you like ...

Re: Unable to create hierarchy in PivotTable

Thu, 02 Apr 2015 20:20:12 GMT

Well, Instead of using PivotTable, can we use Straight Table and achieve the same result without doing too much data manipulation?

Re: Unable to create hierarchy in PivotTable

petter — Thu, 02 Apr 2015 22:28:25 GMT

After fiddling around a little bit I discovered that it was possible to get your desired layout both with Straight Table and with Pivot Table. I had to introduce an extra field when loading the data to number the values for each ItemCategory. Then this number could be used to fill the Chart more efficiently.

The result looks like this and I have attached a new QVW demonstrating how it is done. I dont have more time just now to explain some of the details but feel free to ask if anything is unclear and I will answer most likely tomorrow.

Re: Unable to create hierarchy in PivotTable

petter — Thu, 02 Apr 2015 22:48:57 GMT

And I made a version with some more random test data ... just to see that it works as it should...

Re: Unable to create hierarchy in PivotTable

Tue, 07 Apr 2015 21:17:30 GMT

Thank you. Appreciate your help.

Re: Unable to create hierarchy in PivotTable

Tue, 07 Apr 2015 21:23:58 GMT

Bravo!