Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sort expression problem QV 8.5

Hi could someone please help?

I have a listbox which contains a list of Consultant names from a field called Consultants, e.g.

DR A RASHID

MR C TRUEMAN

DR B MEHTI

DR Z BIGELOW

what I would like to do is sort this list on the surname so it would appear as

DR Z BIGELOW

DR B MEHTI

DR A RASHID

MR C TRUEMAN

I have tried to add a sort expression like so

=Mid([Consultant],FindOneOf([Consultant], ' ', 2))

so this will find the surname after the second space in the string.  If I create a new list box with this expression in as the field it displays as I would expect and is sortable

BIGELOW

MEHTI

RASHID

TRUEMAN

However this does not work if I use this to sort expression, I have tried all the different combinations of checkboxes to no avail

I have tried to make a new field in the load script like so

Mid([Consultant],FindOneOf([Consultant], ' ', 2)) as [Cons_Surname],

The field is added and is available but I still cannot get it to sort correctly.

I need to show the full string in the list box but for it to be sorted on the surname portion of the string

Any ideas?

Thanks a lot

Stephen

1 Solution

Accepted Solutions
Not applicable

Re: Sort expression problem QV 8.5

After more headaches this morning I have come up with a workaround to this problem

The Consultant field in its raw form contains

DR A RASHIDSR0152

MR C TRUEMANSR0153

DR B MEHTISR0154

DR Z BIGELOWSR0152

I now have an expression on the Consultant field in the list box

=Mid([Consultant],FindOneOf([Consultant], ' ', 2),Len(Mid([Consultant],FindOneOf([Consultant], ' ', 2)))-Len(Right([Consultant], 6)))

& ' ' & Left([Consultant], FindOneOf([Consultant], ' ', 2))

This identifies the surname portion of the consultant name first,

strips out the SR0xxx number, then adds on the Prefix and initials.

I then sort on State/Text Ascending

This works fine

like so

BIGELOW DR Z 

MEHTI DR B

RASHID DR A

TRUEMAN MR C

It would be nice to understand why the original solution didnt work though

Thank you

Stephen

5 Replies
danielrozental
Honored Contributor II

Sort expression problem QV 8.5

Try

aggr(Mid([Consultant],FindOneOf([Consultant], ' ', 2)),[Consultant])

Not applicable

Sort expression problem QV 8.5

Thanks for the reply

That doesnt work unfortunately, I get the same results

Any other ideas?

Thank you

Stephen

Not applicable

Re: Sort expression problem QV 8.5

After more headaches this morning I have come up with a workaround to this problem

The Consultant field in its raw form contains

DR A RASHIDSR0152

MR C TRUEMANSR0153

DR B MEHTISR0154

DR Z BIGELOWSR0152

I now have an expression on the Consultant field in the list box

=Mid([Consultant],FindOneOf([Consultant], ' ', 2),Len(Mid([Consultant],FindOneOf([Consultant], ' ', 2)))-Len(Right([Consultant], 6)))

& ' ' & Left([Consultant], FindOneOf([Consultant], ' ', 2))

This identifies the surname portion of the consultant name first,

strips out the SR0xxx number, then adds on the Prefix and initials.

I then sort on State/Text Ascending

This works fine

like so

BIGELOW DR Z 

MEHTI DR B

RASHID DR A

TRUEMAN MR C

It would be nice to understand why the original solution didnt work though

Thank you

Stephen

jedgson
Contributor

Re: Sort expression problem QV 8.5

Stephen,

From my own experience it seems that the sort by expression is looking to sort by some numeric value returned by expression.

You could therefore use hash128() around your original expression

Jay

Not applicable

Re: Sort expression problem QV 8.5

Thanks Jay I will bear that in mind for future reference

Stephen

Community Browser