topic Re: Sorting string field data with numbers in the beginning in QlikView

Sorting string field data with numbers in the beginning

Thu, 01 Sep 2016 12:17:02 GMT

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

Re: Sorting string field data with numbers in the beginning

effinty2112 — Thu, 01 Sep 2016 12:23:27 GMT

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

Re: Sorting string field data with numbers in the beginning

sunny_talwar — Thu, 01 Sep 2016 12:23:55 GMT

Try a sort expression of this:

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

Re: Sorting string field data with numbers in the beginning

aaron_ppmc — Thu, 01 Sep 2016 12:24:27 GMT

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

aaron

Re: Sorting string field data with numbers in the beginning

Thu, 01 Sep 2016 12:25:42 GMT

Andy,

Your 1st idea worked ! Thanks a ton !

-James

Re: Sorting string field data with numbers in the beginning

effinty2112 — Thu, 01 Sep 2016 12:31:15 GMT

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

Re: Sorting string field data with numbers in the beginning

sunny_talwar — Thu, 01 Sep 2016 12:43:37 GMT

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