Sorting within Crosstab - numbers before alpha text

[jannoth]

I'm using the output from a formula as the Row inside a Crosstab.

Results are displayed in two formats:

a) three alpha characters followed by three numberic values;
ABC001
DEF002
XYZ567

b) a numeric value between 1 and 50
1
2
3
4

It works fine but I need to change the sort so that numbers appear before alphanumeric values, as follows:

ABC001
DEF002
XYZ567
1
2
3
4
etc.

Is it possible to do what I'm trying and if so, could someone please give me a clue?

Ta

 

[lbass]

Your example shows numbers appearing AFTER the alphanumeric, not before, so which is it?

-LB

 

[lbass]

If you want the alphanumeric results first, then create a formula like this:

if not isnumeric({table.field}) then
"0" + {table.field} else
{table.field}

Use this as your row field. While it is highlighted->group options->options tab->customize groupname->use a formula->x+2 and enter:

{table.field}

-LB

 

[lbass]

PS. This is the reverse of what your thread title says. The usual sort would put numbers before alpha characters by default--so there would be no issue.

-LB

 

[jannoth]

Thanks GENUS - I'd never have come up with that.

Yeah sorry, stupid title (juggling too many balls at once) - I needed exactly what you have provided.

How does one repay you???

J

PS - sorry about delay - time difference (am in UK).

 

[jannoth]

and - My day has only just started and I'm already dying (2 yr olds with colds won't sleep!!).

Am now 99% there but a new problem arrived, eg:

ABC001
DEF002
XYZ456
1
19
2
29
3

My formula assembles the ABC001 codes (via concatenation) according to values elsewhere. If such values don't exist, I simply display a count (ie the plan numbers). As you'd expect, that formula requires me to convert the counts to text.

But, it's broken your solution and I can't think straight as to what to do about it boo hoo!! I've tried not converting the counters to text, but CR insists.

Any ideas (sorry to ask again)

J

 

[lbass]

if not isnumeric({table.field}) then
"0" + {table.field} else
totext(tonumber({table.field}),"0000") //use the number of zeros that corresponds to the maximum number in the field.

-LB

 

[SkipVought]

[tt]
ABC001
DEF002
XYZ456
1
19
2
29
3
[/tt]
It is important that you understand WHY 19 sorted before 2. It is because you are not really sorting numbers. Rather you are soring numeric CHARACTERS, which is why left padding with ZERO(S) is required, as lbass has indicated above.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jannoth]

Thanks to you both, lbass and Skip. I've modified the relevnt formula and it works perfectly. I knew why 19 was appearing before 2, just couldn't work out how to stop it!

I was barking up the wrong tree altogether! Instead of trying to convert convert convert, I should have been looking at formatting.

Thanks again and seasons greetings to you both