Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Aria
Contributor III
Contributor III

How to conduct muti-level table header through 'mapping' table

I am trying to built a pivot table which have multi-level table headers like shown below. I tried to use the mapping function in the data load scripting area to build each level, yet it ended up with the third level (order book sum, overdue order, undue order) are spreaded not only by 'order book', but also by 'backlog-sum' and 'shipment without invoicing'.

 

The mapping tables I wrote can be seen below. How can I build the table header as the one shown below? Can I build mapping table inside of another mapping table? Or is there any other way to realize this table header?

Thanks a lot!!! 🙂

Mapping1:
Load * Inline [
backlog
backlog-sum
order book
Shipment without invoicing
];

Mapping2:
Load * Inline [
order book
order book sum
overdue order
Undue order
];

Aria_0-1617764993826.png

 

1 Solution

Accepted Solutions
Dalton_Ruer
Employee
Employee

The way you have constructed your table doesn't fit with what you are trying to show. This is what is shown in the data manager:

Mapping.png

You need to think about and model the columns so that there is a hierarchy as desired. Notice how in this code I've designed Category, Sub Category and Minor Category to be the levels reflected in your desired image:

Data:
Load * Inline [
Month, Category, Sub Category, Minor Category, Value
April, Sales Org, ,, 100
April, Backlog, Backlog-sum, , 75
April, Backlog, Order book, order book sum, 30
April, Backlog, Order book, overdue order, 65
April, Backlog, Order book, undue order, 92
April, Backlog, Shipment without invoicing,, 33
April, Budget Sales, ,, 81
April, difference between budget, ,, 19
];

That yields a table that looks like this:

MappingData.png

I then used the Month as the ROW for the pivot table

And the Category, Sub Category and Minor Category as the COLUMNS for the pivot table and Sum(Value) for the Measure

MappingPivot.png

Which is close to what you drew. If I remove the automatic sort for the Category so that it isn't in alphabetical order and isn't in numerical order, it will load it based on the way the values were loaded into the table. 

MappingPivotSortedLoadOrder.png

 

View solution in original post

2 Replies
Dalton_Ruer
Employee
Employee

The way you have constructed your table doesn't fit with what you are trying to show. This is what is shown in the data manager:

Mapping.png

You need to think about and model the columns so that there is a hierarchy as desired. Notice how in this code I've designed Category, Sub Category and Minor Category to be the levels reflected in your desired image:

Data:
Load * Inline [
Month, Category, Sub Category, Minor Category, Value
April, Sales Org, ,, 100
April, Backlog, Backlog-sum, , 75
April, Backlog, Order book, order book sum, 30
April, Backlog, Order book, overdue order, 65
April, Backlog, Order book, undue order, 92
April, Backlog, Shipment without invoicing,, 33
April, Budget Sales, ,, 81
April, difference between budget, ,, 19
];

That yields a table that looks like this:

MappingData.png

I then used the Month as the ROW for the pivot table

And the Category, Sub Category and Minor Category as the COLUMNS for the pivot table and Sum(Value) for the Measure

MappingPivot.png

Which is close to what you drew. If I remove the automatic sort for the Category so that it isn't in alphabetical order and isn't in numerical order, it will load it based on the way the values were loaded into the table. 

MappingPivotSortedLoadOrder.png

 

Aria
Contributor III
Contributor III
Author

Thank you so much Dalton!! It works perfect. 😄