topic Changing column order in QlikView

Changing column order

Thu, 27 Sep 2012 06:10:25 GMT

Hi everyone.

I have a straight table with columns from the dimensions tab as well as the expressions tab. There is one column at the end (dimension column) that will not move to a different position when I drag it. I played around by adding other dimensions after it and they too refuse to move.

Please help / advise.

Thank you.

Regards,

Ashley.

Re: Changing column order

rajni_batra — Thu, 27 Sep 2012 06:16:36 GMT

In presentation Tab there is an option allow Drag and Drop..

Check that column.. hope to help...

Re: Changing column order

Thu, 27 Sep 2012 06:25:42 GMT

Hi.

Thanks for the suggestion. The Drag and Drop option was already checked though...

Re: Changing column order

rajni_batra — Thu, 27 Sep 2012 06:30:44 GMT

this is weird did u try to create a new table does ot behaves the same?? or post ur sample we will check..

Re: Changing column order

Anonymous — Mon, 07 Jan 2013 12:04:54 GMT

I've also experienced this problem, but recreating is not feasible and the table has complex expressions and labels and is part of a template that I hope to deploy relatively seemlessly.

In the attached file shows the Currency dimension appears to the right of the expressions, and not to the right of the Company dimension as expected. Of the 18 expressions, 12 have show conditons set on the presentation properties.

Some observations:

1. The problem occurs after a save, close and reopen, unlike some presentation level issues

2. The problem occurs in desktop as well as on an Ajax client

3. Asked to save on closing the file, after just doing a failed attempt at moving the dimension. Seems something is done but not being displayed

4. It is possible to move dimensions that are already in the expected position to the left of the expressions

Some remedies:

1. Removing the conditonal show on the Presentation properties, and setting to Show always, did not help

2. Deactivating all the expressions that have a conditional show solved the problem

3. Copying and pasting the table help on one occassion, presumably by recreating the table as suggested by Rajni above

4. Once reordering is possible expressions can be reactivated with it still working

5. Adding another dimension after a reordering is possible again results in the problem reoccuring

Remedies 2 and 3 were not always consistent.

Has anyone any idea what might be happening ? Are dimensions ever meant to appear to the right of the expressions ?

The table is presumably corrupted, but works if all the complexity is removed from it. Perhaps just one or more columns are being corrupted.

Jonathan

Re: Changing column order

Anonymous — Tue, 08 Jan 2013 10:42:35 GMT

Found an issue report about this in the Partner Portal entitiled "Hiding dimension or expression in straight table object may result in column order shifting": https://eu1.salesforce.com/articles/Diagnostic/Hiding-dimension-or-expression-in-straight-table-object-may-result-in-column-order-shifting?popup=true

Reason is that "The straight table dimensions and expressions are handled separately. This means that when a dimension is hidden the next dimension in line will take the spot, and if an expression is hidden the next expression in line will take the sport.

Suggested fix is to use expressions for the dimensions.

Jonathan

Re: Changing column order

flipside — Tue, 08 Jan 2013 11:02:38 GMT

Hi Jonathan,

I looked at this a bit yesterday and the file you supplied did indeed have the final dimension positioned after the expressions. When promoting the 4th & final dimension to position three it swaps dimensions 3 and 4 as expected but still has the (new) final dimension after the expressions.

After disabling all expressions then re-enabling them (also had to re-enable the conditional show boxes too) the issue was fixed and seemed to remain so even after closing, rebooting pc etc.. However if you then add a new dimension it goes to the wrong position again and the issue is re-introduced.

Maybe the problem is cuased when adding dimensions after conditional expressions already exist.

flipside

Re: Changing column order

flipside — Tue, 08 Jan 2013 14:43:31 GMT

Try running this macro against the table - seems to fix it for me and for future dimensions added. Only thing is I'm not sure how this DisplayOrder setting is supposed to work, might be worth passing on to Qliktech for their opinion.

sub test1

'get the current sort order

d = cp.TableProperties.DisplayOrder

for i = lbound(d) to ubound(d)

msgbox d(i)

next

set chart = ActiveDocument.getsheetobject("CH1112")

set cp = chart.GetProperties

cp.TableProperties.DisplayOrder = 1

chart.setproperties cp

'get the new sort order

d = cp.TableProperties.DisplayOrder

for i = lbound(d) to ubound(d)

msgbox d(i)

next

end sub

flipside

Re: Changing column order

michael_anthony — Wed, 21 Aug 2013 04:42:50 GMT

I've just come across the DisplayOrder which believe would be very useful for some macro's I use that attempt to add extra dimensions into charts, and sometimes get this scenario where puts into wrong place. I did some testing and think I now get the DisplayOrder setting.

It's an array of integers.

The first value indicates how many dimensions to display first (taking from the dimensions in order).

Second value is how many expressions to display after that (taken from the expressions in order).

Third value is how many dimensions to display after that.

And so on.

For example I setup a chart where shifted dimensions and expressions around. I then got a displayorder of:

4,1,4,1,1,5.

Which matched my columns being:

1st 4 dimensions.

1 expression

Next 4 more dimensions

Next 1 expression

Last 1 dimension

and the final 5 expressions.

I assume setting the value to simply 1 forces Qlikview to show first all the dimensions, then the expressions (in there normal order). When I applied your macro above to this chart, that's what it did.

Re: Changing column order

dgreenberg — Mon, 28 Sep 2015 12:49:42 GMT

Macro does not run for me. Could you please double check the syntax?

The top part is the problem:

d = cp.TableProperties.DisplayOrder

for i = lbound(d) to ubound(d)

msgbox d(i)

Re: Changing column order

dgreenberg — Thu, 01 Oct 2015 15:38:20 GMT

Never mind figured it out

set chart = ActiveDocument.getsheetobject("CH1112")

set cp = chart.GetProperties

need to be at the top of the macro not in the middle.

Re: Changing column order

Thu, 01 Oct 2015 16:41:58 GMT

Hi all

if you use straight table you have flexibility to mix the dimension and expression Oder,

for example if you have the flowing table with

Region,OrderID as dimensions,

QTY, Amount,Discount as expression

Region	OrderID	QTY	Amount	Discount

you can drag them to be like

Region	QTY	Amount	OrderID	Discount

hope it helpful for you,

Regards

Re: Changing column order

reanfadyl — Tue, 07 Jun 2016 21:01:18 GMT

Bump... just in case someone is feeling lazy. Worked a treat, thanks all.

sub fixtablesort

set chart = ActiveDocument.getsheetobject("CH336")

set cp = chart.GetProperties

'get the current sort order

d = cp.TableProperties.DisplayOrder

for i = lbound(d) to ubound(d)

msgbox d(i)

next

cp.TableProperties.DisplayOrder = 1

chart.setproperties cp

'get the new sort order

d = cp.TableProperties.DisplayOrder

for i = lbound(d) to ubound(d)

msgbox d(i)

next

end sub

Re: Changing column order

kwarren1 — Fri, 18 Nov 2016 13:57:39 GMT

Thanks! I just spent 30 min. trying to find a solution to my straight table ordering issues with expressions among dimensions that would not move order until your simple suggestion of dragging and dropping worked like a charm!

Re: Changing column order

jwaligora — Sun, 13 Jun 2021 13:06:58 GMT

I'm on April 2020 SR4. It's been almost 10 years since this issue got posted, and I'm still running into this problem. As noted in earlier comments, drag/drop doesn't work in my case even after unhiding all the columns. Being stuck on a such a seemingly simple step is immensely frustrating, not to mention extremely difficult to explain to management.

Re: Changing column order

greenee — Fri, 07 Jun 2024 09:43:24 GMT

Hi All,
I know it's been a while, but thought I would add this.

I had the same issue and have found a possible solution using the files in the -prj folder.

Create a folder in the same directory as your .qvw file called the same as the qvw, but with -prj as the name of the folder. in this example I created this folder Dimension order 2-prj .
When you save the file Dimension order 2.qvw, it will populate the -prj folder with many files.
Your example file with the problem table, the chart id was CH1112.
Open the CH1112.xml file in the -prj folder and search for <DisplayOrder>
Your original was like this
<DisplayOrder>
<int>3</int>
<int>18</int>
<int>1</int>
</DisplayOrder>

This means that the chart will show the 1st 3 dims, then 18 Expressions, then 1 other Dim.

I deleted the 3rd line and changed the 1st line to be 4, (for 4 Dims)
Like this:
<DisplayOrder>
<int>4</int>
<int>18</int>
</DisplayOrder>
Then save the file and close it.

Close you application and reopen it. When the app opens it checks to see if there is a -prj folder and if so, takes the settings from this folder for the app.
(Thanks Rob Wunderlich for the pointer using your Cookbook site)

The table now has the right sort order for the Dims and Expressions 🙂

Hope this helps, as its taken me a few days to figure this out also with the help of the macro post above, I got to know what the numbers actually meant in the pop up box.

Even by dragging and dropping, disabling and re-enabling expressions and Dims, the error can persist on access point.

Regards
greenee