How to SORT the VARCHAR dataype column's VALUE 1

[CrystalReports9]

Datatype for the column c1 is VARCHAR(3) and having the data like this:

C1
--
12
2
A
11
121
43
S
19B
101
abc

How to sort the value into the following format....
like number to be sorted in ascending order
then the character value to be sorted.

How to get the following output?

C1
--
2
11
12
101
121
A
S
abc
19B

 

[synapsevampire]

From what I can gather, you are sorting by the numeric value on the right side of alpha characters, then by CAPS on alpha, then by lowercase alpha, and finally by left side or alpha numerics.

This is a bizarre sortation, please confirm or define the sortation rules.

-k

 

[dpatrickcollins]

How bout if I get you halfway there. Use this formula as the basis for sorting.

If isnumeric({Table1.fld}) Then
tonumber({Table1.fld})
else 9999999999999

The only thing this formula does not do is what your last items appear to reflect: sort the non-numeric values by captial, then by lowercase, then by number.

 

[CrystalReports9]

Hi...still not solved the problem....I would like to redefine like the following:

Sample Data
------------
188 HOS_DIS G 19 3223
188 HOS_DIS T 19 5669
188 HOS_DIS G 19B 1221
188 HOS_DIS T 19B 1318


Output:
--------
Municipality: 188 <----Group 1

Group: Hospital (HOS_DIS) <----Group 2

Status : G <----Group 3

Details:
--------
19 3223
19B 1221


Status : T

Details:
--------
19 5669
19B 1318

 

[synapsevampire]

Given the example data and grouping, that's precisely what the output would be providing that you're sorting on the field containing:

19 3223
19B 1221

But again, you need to supply the sortation rules, your original post has completely different data sets which you aren't addressing here.

-k

 

[CrystalReports9]

Hi

The sortation rules like

first sort the numbers
then sort the Char Alpha

I hope i define the sortation rule. Thank u synapsevampire.

Rgs
Anto

 

[synapsevampire]

Your rules go against your example:

2
11
12
101
121
A
S
abc
19B

A standard char alpha sort would return lowercase before uppercase, and 19B would sort before an A.

Think through what your rules are and write them down for each instance as I had tried to do for you earlier.

-k

 

[CrystalReports9]

Hi Synapsevampire

I created the formula field (for the sort order field) like

IF NumericText({WK_BUDGET_FINAL.RPRT_JURISDICTION_CD}) Then
{WK_BUDGET_FINAL.RPRT_JURISDICTION_CD}
Else
{WK_BUDGET_FINAL.RPRT_JURISDICTION_CD}

Later i create the new group for the &quot;formual field&quot; and select the &quot;Underlay Following Section&quot; in the &quot;Section Expert&quot;....

I feel like i got the solution....Now the sorting is perfect. But i am in testing with various combination....

Pls let me know your suggestion. Thank U.

 

[synapsevampire]

This formula:

IF NumericText({WK_BUDGET_FINAL.RPRT_JURISDICTION_CD}) Then
{WK_BUDGET_FINAL.RPRT_JURISDICTION_CD}
Else
{WK_BUDGET_FINAL.RPRT_JURISDICTION_CD}

Doesn't do anything different than natural order as you do the same thing on both sides of the ELSE...

-k

 

[CrystalReports9]

Hi Synapsevampire

It does the different from the Natural sort order of VARCHA datatype column's Value.

After create the new group for the above said &quot;formual field&quot; and select the &quot;Underlay Following Section&quot; in the &quot;Section Expert&quot;, what i feel is CR take care of the sorting order based on the condition in the formula field

NumericText({WK_BUDGET_FINAL.RPRT_JURISDICTION_CD})

If this condition success then internally it will sort the numeric value first ELSE if the condition fails then sort the char and then alpha values.

I feel CR works in this way. Because without set any condition / formula / group, if we try to sort the VARCHAR field value then &quot;sorting option&quot; behaves its natural way (like mixing all the type).

Is that my assumption is right ?

 

[pelajhia]

dpatrickcollins - just what I needed - thanks for lifesaving formula