topic How to conduct muti-level table header through 'mapping' table in Connectivity & Data Prep

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

Aria — Wed, 07 Apr 2021 03:09:31 GMT

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
];

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

Dalton_Ruer — Wed, 21 Apr 2021 14:36:59 GMT

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:

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:

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

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.

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

Aria — Thu, 22 Apr 2021 05:07:54 GMT

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