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: 
Not applicable

Unable to create hierarchy in PivotTable

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?

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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.

2015-04-03 #1.PNG

View solution in original post

15 Replies
Not applicable
Author

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

by hic

kamal_sanguri
Specialist
Specialist

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

Thanks,

Kamal


Anonymous
Not applicable
Author

You have to use all three as dimensions.

kamal_sanguri
Specialist
Specialist

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.

Anonymous
Not applicable
Author

All three must be in dimensions to get the hierarchy view

petter
Partner - Champion III
Partner - Champion III

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 ....

2015-04-02 #1.PNG

2015-04-02 #2.PNG

2015-04-02 #3.PNG

Not applicable
Author

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.

Not applicable
Author

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

petter
Partner - Champion III
Partner - Champion III

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