Sorting string field data with numbers in the beginning

[#1]

Hi ppl,

I have a qvd with a column of type string which has data starting with numbers like 2.Ball 1.Apple 21.Doll 10.Cat.

When I am trying to display this along with another column (which contains count ) in Qlikview Straight table by sorting the values based on the first column, the data is displayed as 1.Apple 10.Cat 2.Ball 21.Doll . But I want it to be displayed as 1.Apple 2.Ball 10.Cat 21.Doll .

Is this possible when the numbers are part of the string column ? If so please help me to achieve. I have tried all combinations of sorting options in the properties dialog. Please find the snapshot of the table as well as properties dialog for the same

TIA,

James

[effinty2112 · Accepted solution]

Hi James,

Here are two suggestions:

1/ Sort by expression, try : SubField(FieldName,'.',1)

2/ If the data is loaded in the order you want from the qvd then Sort by Load Order.

Cheers

Andrew

[sunny_talwar]

Try a sort expression of this:

SubField(FieldName, '.', 1) * 1

[aaron_ppmc]

hy James,

extract the numbers at the beginning of your string into a new column and sort it by Expression using the new column.

give it a try

br

aaron

[#5]

Andy,

Your 1st idea worked ! Thanks a ton !

-James

[effinty2112]

Hi Sunny,

                    Is the multiplication by 1 to ensure that the expression is interpreted numerically? I hesitated in my answer for a second and considered wrapping it in Evaluate().

Cheers

Andrew

[sunny_talwar]

Yes the * 1 is for changing the text field into numeric. Alternatively, I have used Num#() function to do the same