topic Listbox Expression sort problem in QlikView

Listbox Expression sort problem

Anonymous — Sat, 03 Apr 2010 22:42:44 GMT

Hi guys,

I've faced a problem trying to sort values in a listbox populated by the expression:

=if(EntryType = 'PR',
Project,
null()
)

The sort rule is:

State = "Auto Ascending" Text = "A->Z"

Whenever any value from other fields is selected, the values of the listbox remain on their places although the possible values should be displayed first according to the sort rule.

The listbox with any field gets sorted perfectly well while it seems that it does not like expressions for sorting.

Are there any ideas how to solve the problem?

Thanks.

Valera

Listbox Expression sort problem

deepakk — Sun, 04 Apr 2010 10:54:14 GMT

Just Change the State = "Auto Ascending" to

State = "Ascending"... It will work Fine.!!

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 11:29:59 GMT

That would be too easy. 🙂

I've already checked all the possible combinations with the STATE option in the sort rule - it seems to be simply ignored.

With any combination of STATE properties I always get the same result:

:
Text = "A -> Z"

State = "AutoAscending", Text = "A -> Z"

State = "Ascending", Text = "A->Z"

If I specify just the State option in the sort rule the listbox remains simply unsorted.

Is there a solution for this problem?

Thanks.

Valera

Listbox Expression sort problem

deepakk — Sun, 04 Apr 2010 12:00:13 GMT

I am QV SR3 and it is working fine for me. Which version of Qlikview are you using ?

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 12:31:58 GMT

I also use QV SR3.

However, I want to emphasise that the sorting works perfectly fine in a listbox with field name. I needed to exclude some field values and used the expression. And the sorting stopped working! 😞

Listbox Expression sort problem

deepakk — Sun, 04 Apr 2010 12:40:04 GMT

I think i Got it...!!!

Just used the same Expression in the Expression tab inside sorting. It seems to be working..!!!

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 12:49:07 GMT

Yep! 🙂 Indeed, this seems to work!

Thanks! 🙂

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 13:02:01 GMT

Well, the primary objective - to put the possible values at the beginning and the excluded values at the end of the list - has been accomplished.

But I see that the sorting itself behaves strangely. This is a "sorted" list:

I can't figure out the reason why the sorting should "restart" when empty string is found! The whole list results split in 2 sorted sublists.

Why can this happen?!

Listbox Expression sort problem

deepakk — Sun, 04 Apr 2010 14:07:07 GMT

hi,

Why are you using the null() in the expression any specific reason. Remove it and check

eg :

=if(EntryType = 'PR',
Project
)

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 14:33:24 GMT

I seem not to like implicit values, so prefer explicit definition of the result to be sure to have the values I want. 🙂

In this case, however, there was no specific reason for NULL(), so I removed it. But the resulting "sorted" list remains as it was before - split in two chunks... 😐

Listbox Expression sort problem

deepakk — Sun, 04 Apr 2010 14:54:04 GMT

I am not sure but try this...

=if(EntryType = 'PR' and len(trim(Project)) >0, Project)

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 15:16:27 GMT

Well, as expected, the empty string disappeared, but the sort order remained intact:

Listbox Expression sort problem

deepakk — Sun, 04 Apr 2010 16:03:17 GMT

try this...

=if(EntryType = 'PR' and len(trim(Project)) >0, trim(Project))

Apply the same in Expression Sorting and say Ascending.. it should work

Listbox Expression sort problem

Anonymous — Sun, 04 Apr 2010 16:28:14 GMT

Hi Deepak,

Thanks for the suggestion. However, it does not change the situation. The sorting keeps not working.

Moreover, I can't continue excluding the empty strings from the listbox. In fact, I have a field Project and a field ProjectDescription. So, I display the project descriptions and there are some projects with empty descriptions. Still, I want to see them.

The Project table contains the fields Project, EntryType, ProjectDescription. So, by using the EntryType field I can populate the listbox only with the relevant project descriptions.

That's why my expression is:

=if(EntryType = 'PR', ProjectDesc)

As for the sort rule, it seems that I am forced to use both the State option, the Expression option and the Text option. If the Text option is excluded, the list remains simply unsorted. If the Expression option is excluded, the list remains sorted well, but the excluded items are simply marked as excluded and not moved to the end of the list.

This seems to be a kind of a QV bug or there is problem in the expression itself... Any further ideas?! 🙂

Thanks! 🙂

Listbox Expression sort problem

deepakk — Mon, 05 Apr 2010 06:47:51 GMT

if you can attach the applictaion I can give it a try.........

Listbox Expression sort problem

Anonymous — Mon, 05 Apr 2010 07:57:23 GMT

The easiest approach would likely be to define the expression as a field in the script already and it will sort correctly in the listbox when you add it in your layout.

Listbox Expression sort problem

Anonymous — Mon, 05 Apr 2010 10:41:28 GMT

Hi Johannes,

I've tried to create an "artificial" field as an expression and could not find a way to do it. The only possibility was creating a group with an expression. When I use this group in the listbox the sorting seems to work perfectly.

However, the use of the group implies the display of a group symbol (either Cyclic or DrillDown) on the listbox which is confusing.

Are there other ways to create "artificial" fields?

Thanks.

Listbox Expression sort problem

Anonymous — Mon, 05 Apr 2010 10:58:52 GMT

Anyway, guys, find attached the application with the problem.

I guess there is some problem with the data because when I tried to load fewer records the sorting seemed to work fine... I'm definitely missing something. 🙂

Thanks for help! 🙂

P.S. When you open the application, you will see this list already:

Listbox Expression sort problem

deepakk — Tue, 06 Apr 2010 14:53:14 GMT

HI Valera, I dont the reason but if you remove the State from sorting and Use Descending in Expression then it seems to be working fine...

But i dont know y its behving in this fashion....

Listbox Expression sort problem

Anonymous — Tue, 06 Apr 2010 15:11:36 GMT

Thanks for the suggestion, Deepak.

I removed the STATE from sorting, but then I have the old problem of excluded values inside the list (not in its end) back.

And, strangely, I see that the list itself is not sorted properly when EXPRESSION option is used. The list is sorted well only if its values are treated as TEXT. But the STATE option is ignored by QV in this case.

Thanks for suggestion.