Create table in QV with multiple header rows

[#1]

Hello

Does anyone know whether it is possible to create a straight/pivot table in QV that looks exactly the same as the attached spreadsheet? The sheet has multple header rows and it is this data that I want to import and display.

The only way I think of is to tranpose the data in the attached spreadsheet so that the column headings become data values, then use the columns that contain those values as column dimensions in the table. But is there a way of doing this without transforming the attached data?

Many thanks.

[richard_chilvers]

I have never found a way to do this ! Of course, it is possible to have column headers over several lines. But this seems to be a drawback in QV and is often needed for example a single header for the year, with the 12 month columns beneath.

Perhaps it is included in version 11 ?

[matt_crowther · Accepted solution]

Apologies; I got a little ahead of myself earlier and forgot about the load from a table laid out like that in the first place.

Anyhue; a little fiddling later and hopefully the full solution is attached this time.

If your real-life situation isn't much different to this then you should be OK otherwise it will end up very complex; essentially I'm ignoring the top 2 rows of the Excel table and creating syntetic versions of them in the Pivot table (laid out as before) and using an IF t re-assign the correct column.

I recommend looking at ValueList; it's a really pwoerful tool.

Hope that helps,

Matt - Visual Analytics Ltd

[richard_chilvers]

Thanks !

Have got it working

[richard_chilvers]

James

Perhaps you could mark Matt's reply as Correct or Useful

Good luck !

[#6]

That's great Matt. Very helpful!

[#7]

Hi, and thanks for the helpful discussion.

Alas, i am facing more challenging reality - I have different set of expressions, 4 expressions for the first value in my ValueList, 5 for the second value, 6 for the third and 5 expressions for the fourth value. In this case I can't write the IF condition in the way you have shown.

I have tried to hide expressions by using conditional

ValueList ('Closed','Reserved','Total','Ratios') ='Closed'

for my first four expressions, etc., but that didn't work, they still get shown under the other values of the synthetic dimension.Could you help me in resolving this.

Regrads,Borislav

[andy]

Me too have different expressions in the different header-groups.

That is why I like this approach better and on top of that it is cleaner and more straightforward.

http://community.qlik.com/thread/3098

Download the file testPivotHeaderGroups.qvw its the solution.

/Andy

[#9]

Thank you, Andy, it looks promising and I am testing it now in my case, thanks!

Thank you, Amit and John, as well.