topic Sort with zeroes leading values in QlikView

Sort with zeroes leading values

Fri, 07 Aug 2015 10:52:13 GMT

Hi,

i have a table with a field "code" like this:

00A

01A

02A

03A

If i put this field in a table o in a list box and set order by TEXT, i have this result:

00A

01A

02A

03A

that's not correct... value 2 must be after 02A and after 03A.

I already try to add the function TEXT(code) in loading.

Any ideas?

Thanks

Re: Sort with zeroes leading values

Kushal_Chawda — Fri, 07 Aug 2015 11:01:59 GMT

what should be the order?

Re: Sort with zeroes leading values

Fri, 07 Aug 2015 11:04:50 GMT

The first i typed :

00A

01A

02A

03A

Re: Sort with zeroes leading values

Anonymous — Fri, 07 Aug 2015 11:10:41 GMT

if it is regular that only 1 character can occur you may try this in sort Expression ascending:

if (isnum(right(code,1)), code, left(code,len(code)-1))

Re: Sort with zeroes leading values

Digvijay_Singh — Fri, 07 Aug 2015 11:17:06 GMT

Do you have any other check box checked bcz selecting TEXT only option in sort is giving me different result

Re: Sort with zeroes leading values

Kushal_Chawda — Fri, 07 Aug 2015 11:26:19 GMT

Something like this

Data:

LOAD *,

Data2&Data1 as Sort;

LOAD *, if(IsNum(Data),Data) as Data1,if(IsText(Data),Data) as Data2 Inline [

ID,Data

1,00A

4,03A

2,01A

3,02A

5,2

6,3

7,02A

8,03A

9,00A ];

Now Use Sort in listbox

Re: Sort with zeroes leading values

Gysbert_Wassenaar — Fri, 07 Aug 2015 11:53:51 GMT

If the sort order is important then first load the values in the order you want them. Then you can use Load Order as sort option in the listbox object for that field.

Re: Sort with zeroes leading values

jonathandienst — Fri, 07 Aug 2015 12:06:12 GMT

Enable sort by expression, and use this expression:

=Right('000' & code, 3)

(This should not be necessary, but it will work, and will not require you to know all the values before hand, like many of the solutions above)

Re: Sort with zeroes leading values

Kushal_Chawda — Fri, 07 Aug 2015 12:12:58 GMT

hi jontydkpi I think it will not work when order of the data is not fixed like the data which I have loaded in my post

Re: Sort with zeroes leading values

sasiparupudi1 — Fri, 07 Aug 2015 13:10:27 GMT

do they all have a suffix A? Could you post your data please?

Re: Sort with zeroes leading values

jonathandienst — Fri, 07 Aug 2015 13:25:06 GMT

It does sort the data the way the OP requested, and it does not require you to know all the codes (and manually sort them) beforehand. It does assume that nothing is longer than 3 characters.

Re: Sort with zeroes leading values

Fri, 07 Aug 2015 14:40:07 GMT

Thanks all for reply,

but data are not so simple, i wrote only an example. The field lenght can be more than 3 chars and can contains numbers and letters (and more than one "0" in the left).

I resolve in this way: in the table and in the field box i use then expression "=text(code)" instead of "code".

Then sorting by TEXT and it works.

Re: Sort with zeroes leading values

antoniotiman — Fri, 07 Aug 2015 15:01:00 GMT

Hi Andrea,

in Tab Sort -> Expression

Rank(Field) desc

Regards,

Antonio

Re: Sort with zeroes leading values

awhitfield — Fri, 07 Aug 2015 15:12:56 GMT

Hi Andrea,

just loaded the data like:

Sort:
Load * Inline
[
Data
00A
01A
02A
03A
2
3
];

Added to a list box with the default sort and it's appears to be as you wish

Confused!

Andy