topic Re: Sorting Converted Numbers in QlikView

Sorting Converted Numbers

richard_chilvers — Thu, 09 Jan 2014 15:59:24 GMT

I am creating a field by concatenating 2 loaded fields and converting to a number as follows:

LOAD NUM#(NUM#(XSDP_XCET_ID)&NUM#(XSDP_DATE_POSTING)) AS CRUISEDAY

When I create a list box for CRUISEDAY, sorted numerically, this is the result:

21800000007641549

11000000000141549

11000000001341549

11000000000241549

11000000001341550

11000000000141550

21800000007641550

11000000000241550

11000000001341551

11000000000241551

21800000007641551

11000000000141551

11000000000141552

21800000007641552

11000000001341552

11000000000241552

etc.

It seems to sort on the XSDP_DATE_POSTING value (ie. the last 5 digits). It seems my conversion to number is not working and this is vital as I'm trying to use it for an INTERVALMATCH.

Any advice please ?

Re: Sorting Converted Numbers

swuehl — Thu, 09 Jan 2014 16:05:37 GMT

I think your num#() function just won't parse the number (too many digits). Have you double checked that it is actually numeric? What about a text sort?

Re: Sorting Converted Numbers

richard_chilvers — Thu, 09 Jan 2014 16:10:20 GMT

Hello again swuehl - you are giving me alot of good advice !

Too many digits? Where does it say what the limit is?

Yes, a text sort works, which leads me to believe CRUISEDAY is not numeric and therefore it won't work in my INTERVALMATCH.

Any suggestions?

Re: Sorting Converted Numbers

swuehl — Thu, 09 Jan 2014 16:15:55 GMT

Try

LOAD Evaluate(NUM#(XSDP_XCET_ID)&NUM#(XSDP_DATE_POSTING)) AS CRUISEDAY

to get a number, but probably you will lose some significant digits anyway. But the intervalmatch might still work ok depending on the size of your interval.

Re: Sorting Converted Numbers

richard_chilvers — Thu, 09 Jan 2014 16:23:54 GMT

That seems to work ! At least the INTERVALMATCH is working and I just need to make sure it is working how I expect in all circumstances !

Do you suggest I treat NULL values in a special way ?

Is the size limitation defined ?

Thanks again.

Re: Sorting Converted Numbers

swuehl — Thu, 09 Jan 2014 16:46:15 GMT

I think the limit is <= 14 digits. There are some few threads here in the forum that discusses this.

I know there is a similar limitation in other parsing functions / programming languages, just can't recall the deeper causes for that right now.

Re: Sorting Converted Numbers

richard_chilvers — Thu, 09 Jan 2014 17:00:39 GMT

OK - thanks.