topic Re: Re: Re: Pivot table sort in QlikView

Pivot table sort

Fri, 24 Oct 2014 15:23:37 GMT

Hi all,

In my load script a column 'HashVal' is calculated to define the ordinal position of the rows.

In a straight table I can define a primary sort on 'HashVal' (ascending) and it renders correctly like this:

But I can't seem to get it to appear in the correct sequence in a pivot table (fast type change):

for example 2058 should appear before 2059, 2062 before 2063 etc.

I've tried unchecking all the sort values in properties and then using expression min(HashVal) for combinations of the dimensions but to no avail.

I've only included HashVal for visibility - I don't actually want to see it in the pivot table (nor want a straight table option).

Can anyone help please?

Regards,

Gordon

v11sr8

Re: Pivot table sort

alexandros17 — Fri, 24 Oct 2014 15:25:25 GMT

In sorting expression use simply HashVal (not min of it) and ... let me know

Re: Re: Pivot table sort

Fri, 24 Oct 2014 15:43:58 GMT

Hi Alessandro,

Thanks for the reply.

Just 'HashVal' in the expression doesn't work either.

I've attached an extract from the straight table if you want to see the problem for yourself (my way of saying I'd appreciate the help!)

regards,

Gordon

Re: Re: Pivot table sort

alexandros17 — Fri, 24 Oct 2014 15:53:36 GMT

An idea, just try:

use hashval as expression (it must be the first expr.) then order by Y value

Re: Pivot table sort

Fri, 24 Oct 2014 15:54:40 GMT

Gordon Alessandro answer is the right way to do it but you have to put hasval in expression sort for all the dimensions not just for one

Re: Pivot table sort

Anonymous — Fri, 24 Oct 2014 15:56:07 GMT

In a pivot table sorting a sub dimension [i.e.HashVal], in preference to the upper dimensions does not make logical sense.

Use a straight table and it should work.

Or in your pivot table promote the HashVal dimension to the top.

Re: Re: Pivot table sort

Fri, 24 Oct 2014 16:05:34 GMT

Hi All,

Thanks for the replies.

I tried adding the expression to all the dimensions and then checking 'Y value' but it seems to mess it up even more:

Bill - you can't prmote columns in sort for pivot tables.

Regards,

Gordon

Re: Pivot table sort

Anonymous — Fri, 24 Oct 2014 16:10:49 GMT

Promote the dimension on the dimension tab.

Re: Re: Pivot table sort

Fri, 24 Oct 2014 16:16:36 GMT

Hi Bill,

If I do that it then pivots on HashVal:

This needs to be a pivot table and I dont want to display HashVal anyway. HashVal simply defines the logical sequence of the data.

This should be easy!

Regards,

Gordon

Re: Re: Re: Pivot table sort

Mon, 27 Oct 2014 16:20:49 GMT

Hi all,

I still can't get this to work unfortunately and would really appreciate any help.

Perhaps an explanation is required. I'm trying to pull together some security reports that identify permissions by user group for items on menus. I want to mimic the sequence to make it easy to cross reference. Some menus/items won't appear in the security report for all user groups e.g. no access for a group of items means the lower level items won't appear; I resolved this (I believe) by creating an autonumber based on the concatenation of all the levels.

This is an example of a menu for a module:

The items for 'Places' and 'Common Forms' are not grouped as such, whereas those items e.g. Journals/Reports etc do.

Attached is a qvw which contains the transformed security report for a single user group and hopefully demonstrates the sorting issue.

If anyone has the time, I'd sure appreciate it.

Reagrds,

Gordon

Re: Re: Re: Re: Pivot table sort

Anonymous — Mon, 27 Oct 2014 18:35:34 GMT

Gordon

Looks like an extra dimension called say Level0 would help.

In the attached qvw I have frigged this as a Calculated Dimension with expression :

=if ( match(Level1,'Cases','Claims','Recalls','Maintenance Plans') , 'Places' ,

if ( match(Level1, 'Journals' ) , 'Journals' ,

if ( match(Level1, 'Reports' ) , 'Reports' ,

'The Rest'

) ) )

Calculated Dimensions are not something I would recommend, but you could sort this Level0 creation in your load script.

Other things could also be tidied in the load script like after doing the above have a preceding load with something like :

if ( Level0 = Level1 , Level2 , Level1 ) as Level1 ,

To avoid Level0 & Level1 displaying as the same, by putting the Level2 value in Level1. This would need similarly need sort for the other levels.

Bill

Re: Pivot table sort

kuba_michalik — Mon, 27 Oct 2014 20:33:47 GMT

So, the mis-sorted rows all have null dimension values, right? Or are those actual values (empty strings, spaces)?

Because if they are null values, then maybe I'm crazy, but try switching the pivot table to show subtotals on top. Yeah, I know you don't have any.

Re: Pivot table sort

kuba_michalik — Tue, 28 Oct 2014 10:01:33 GMT

OK, the above is irrelevant, I haven't checked the example qvw first, sorry. I think it's an issue I have encountered before. Dimension sort order does not depend on pivot table context. Put a listbox somewhere, with for example Level2, and sort it by Min(HashVal). The order you get here will be preserved in pivot table no matter what, if you use the same sort expression for dimension in pivot table. So for example, Recalls will always be above Maintenance, even if for Level1=Setup it should actually be below.

My workaround was rather hacky. In script, I have appended a number of spaces to each subsequent occurence of a same value in a single hierarchy level (first occurence as is, second + one space, third + two spaces, etc.) That way, they displayed exactly the same (trailing spaces are not shown - you don't get the ... because of them), but the values were actually distinct and could be sorted the way I wanted. In case you want to allow selection on those fields and do not want to see duplicates in listboxes, associated fields without the spaces can be kept in the data model.

Re: Pivot table sort

Wed, 05 Nov 2014 15:58:52 GMT

Hi Bill and Jakub,

Thanks very much for your responses - sorry for my late reply.

I just couldnt get the pivot table to respond to a sort sequence - it always seemed to want to render in 'load order' which meant of course that any value previously loaded would appear out of sequence when it appeared on subsequent occasions.

My 'solution' was to force a number of spaces on the end of 'Item' to make it 'unique' based on an ordinal position (relative to the dimension hierarchy) to try to sort on this structure. Where there was a 'collision' i.e. 'Claims ' (3 spaces) had been previously loaded this wouldnt matter as it still represented the 3rd ordinal position in the list (relative to the hierarchy). This is a sample of the code:

where e.g.:

'lL2' represents the selections for a list box (the pivot table was made 'read only')

'Level2' the 'Item' with a number of spaces appended to make it 'unique' in the list relative to the hierarchy (Level1,Level2,Level3 etc)

anL2 the ordinal position in the list relative to the hierarchy (on which Level2 would in theory be sorted in the pivot table)

I get the feeling it should have been easier but I tried everything and just glad to get past the issue!

Regards,

Gordon