Discussion Board for collaboration related to Creating Analytics for QlikView.
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
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
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
Thanks a lot
Go to Solution.
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,
I then sort on State/Text Ascending
This works fine
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
aggr(Mid([Consultant],FindOneOf([Consultant], ' ', 2)),[Consultant])
Thanks for the reply
That doesnt work unfortunately, I get the same results
Any other ideas?
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
Thanks Jay I will bear that in mind for future reference