topic Multi Box Sorting problem in QlikView

Multi Box Sorting problem

Thu, 22 Apr 2010 15:58:10 GMT

I have a situation when I need many different calendars, Like GMT, EST, User specific TZ.

For each of the calendars' fields there are a prefixes like "GMT_", "EST_".

There is a prefix field in the system like:

PrefixTable:
load * inline [
TZPrefixName,TZPrefix
GMT,GMT_
EST,EST_
Session TZ,Session_
Station TZ,Station_
];

There are variables for current calendar fields presentation, like:

SET vCurrTZPrefix=TZPrefix;

SET vDateTime = "=$(vCurrTZPrefix) & 'DateTime'";
SET vDMDate = "=$(vCurrTZPrefix) & 'DMDate'";
SET vDMYear = "=$(vCurrTZPrefix) & 'DMYear'";
SET vDMMonth = "=$(vCurrTZPrefix) & 'DMMonth'";
SET vDMWeekDay = "=$(vCurrTZPrefix) & 'DMWeekDay'";
SET vDMDay = "=$(vCurrTZPrefix) & 'DMDay'";
SET vDMHour = "=$(vCurrTZPrefix) & 'DMHour'";
SET vDMMinute = "=$(vCurrTZPrefix) & 'DMMinute'";
SET vHourCount = "=$(vCurrTZPrefix) & 'HourCount'";
SET vStartDT = "=$(vCurrTZPrefix) & 'StartDate'";
SET vEndDT = "=$(vCurrTZPrefix) & 'EndDate'";

In a dashboard, there is a Multi Box with the expressions instead of real fields. Everything works fine. There is one problem only - sorting of values in the multi box expression based fields. Instead of expected sequence for week days: Mon, Tue, Wed, Thu, Fri, Sat, Sun; I have got: Wed,Thu,Fri,Sat,Sun, Mon,Tue. There is same problem with a month days.

To understand what I'm talking about just take a look at my attached example, where I've made an emulator of the situation.

Is there any idea how to deal with the sorting, or may be there is another solution for whole approach?

QV version is 9.00.7440.8 SR3 x64

Multi Box Sorting problem

Fri, 23 Apr 2010 03:02:34 GMT

So, nobody knows how to deal with it?

Multi Box Sorting problem

Fri, 23 Apr 2010 20:16:21 GMT

Does it mean there is no solution for this? Or nobody faced the problem?

Multi Box Sorting problem

johnw — Fri, 23 Apr 2010 21:01:53 GMT

Works fine for me, version 8.50.6231.5:

Multi Box Sorting problem

Sat, 24 Apr 2010 02:45:21 GMT


John Witherspoon wrote:
Works fine for me, version 8.50.6231.5:
<div></div>

This one works with me too. Try another one. The problem with this:

And this:

Multi Box Sorting problem

Mon, 26 Apr 2010 09:51:18 GMT

It seems it isn't possible to sort on a calculated expression in a Multibox. See e.g.

http://community.qlik.com/forums/p/22103/84509.aspx#84509

Multi Box Sorting problem

Mon, 26 Apr 2010 13:10:37 GMT

It's not a solution for me. because there is no particular dimension for the multi box. The expression is based on an end user selection.

Multi Box Sorting problem

johnw — Mon, 26 Apr 2010 19:12:38 GMT


Nick Bor wrote:This one works with me too. Try another one. The problem with this:

Oh! That second multi-box doesn't even show in 8.5, so I thought you were talking about the first one. Sorry about my confusion. I can see it in 9.0.

Why it won't let you select a sort order, I don't know. Hopefully that will be a future feature.

Fortunately, it's easy to work around. The sort order appears to be the load order. So all you need to do is load each of these fields in the order you want it presented BEFORE you get to your main load. In other words, just build a sort order table, then drop it at the end. I've done that many times for various reasons, and it appears to work fine here. See attached.

[Sort Orders]:
LOAD recno() as DMDay
AUTOGENERATE 31
;
CONCATENATE ([Sort Orders])
LOAD recno() as DMMonth
AUTOGENERATE 12
;
CONCATENATE ([Sort Orders])
LOAD weekday(date#(20100131,'YYYYMMDD')+recno()) as DMWeekDay
AUTOGENERATE 7
;

... your regular load here ...

DROP TABLE [Sort Orders];

Multi Box Sorting problem

Mon, 26 Apr 2010 19:28:22 GMT

That's nice.

It doesn't work in version 10 beta2 either.

Thank you again.

Multi Box Sorting problem

Mon, 26 Apr 2010 19:37:55 GMT

Wait a second. Where are the months' names?

Multi Box Sorting problem

johnw — Mon, 26 Apr 2010 19:59:31 GMT


Nick Bor wrote:Wait a second. Where are the months' names?

Oops. I needed to treat months like I treated weekdays in order to get the month names:

CONCATENATE ([Sort Orders])
LOAD month(addmonths(date#(20091201,'YYYYMMDD'),recno())) as DMMonth
AUTOGENERATE 12

Multi Box Sorting problem

Mon, 26 Apr 2010 20:13:54 GMT