topic Re: Create an Expandable Table in QlikView

Create an Expandable Table

Fri, 24 Jun 2016 20:20:32 GMT

Hello everyone,

This is how my pivot table looks like:

John Doe John Smith Jane Doe

June

July

August

However, is there a possible way to make it look like something this:

Employees

June

July

August

And then under employees, there will be that + sign to expand and see what's inside Employees

Thank you

Re: Create a

swuehl — Fri, 24 Jun 2016 21:14:22 GMT

Create a field in your data model with a constant 'Employees', like

LOAD

Employee,

'Employees' as EmployeeDim,

...

Then use EmployeeDim and your existing Employee field as dimensions in your pivot table chart (plus the Month field).

Re: Create an Expandable Table

johnw — Fri, 24 Jun 2016 22:07:44 GMT

I'd probably do it just as swuehl‌ says.

But just to toss out a different idea that isn't exactly what you asked for but is similar in flavor, sometimes I'll create a value of 'Total' in a field [Total] disconnected from my data set, then create cyclic group(s) with [Total] and whatever field(s) I might want to break that total down by in the chart(s). In this case, something like:

[Employees]: LOAD 'Employees' as [Employees] AUTOGENERATE 1;

And then create an EmployeeGroup with [Employees] and [Employee] and use the group as the dimension.

But it's a pivot table, so people will probably be more used to looking for the + button, and will know what it does where they won't know until they try it what clicking on the little cycle icon will do. So probably best to stick with just putting both fields in the pivot table. You could still leave [Employees] disconnected from the rest of your data model, though. A little more script, a tiny bit less memory used. I doubt speed would be any different either way, but I don't know. The 'Total' approach is really more for when you have more than one way you might want to break the data down by, or you might just want to look at the total.

Re: Create an Expandable Table

swuehl — Fri, 24 Jun 2016 22:19:10 GMT

Just as a side note, John, I think a constant value won't occupy any memory (in the bit stuffed pointer) in the record table (that's something I learned recently in a discussion following one of Henric's blog posts). You can see this when looking at the field bit information when storing this table to a QVD.

https://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointers#start=50

Symbol tables should be mostly the same in both cases.