Sorting

[qw_johan]

Hi!

I need help sorting moths. I have read other posts but havn't been able to find an answer that works.

Years and dates sare sorting fine...but not months. Why?
I try to get it to sort in the properties but nothing seems to get the months (manad) sorted right. All data are in individual listboxes.

ODBC CONNECT TO [MS Access Database;DBQ=J:\data.mdb];

Bilbokning:
LOAD ar,
 dag,
 manad,
 nr;
SQL SELECT ar,
 dag,
 manad,
 nr
FROM bokuppgifter;

Any help appreciated...

Thanks

[Miguel_Angel_Baeyens]

Hello,

First, make sure the field is loading properly and that the results are as expected (numbers with or without leading blanks or zeroes, text trimmed both left and right...) I'd suggest you to check that doing the following if the "manad" field is text when loading

Trim(manad) as manad,

And the following if it's a number

Num(manad) as manad

Hope this helps.

[qw_johan]

Thanks for your quick reply.
But that didn't help. I am loading my data from an Access database.

My qvw file can be downloded from here:
http://www.joad.se/Test.qvw

Thanks

[#4]

This always alphabetical, because this field as text and not numbers.
What is the format of the manad field in the database?

[#5]

Be careful, month variable is a string call trim(manad)

In the script change

trim(manad) as manad

JJ

[qw_johan]

"manad" in the database is a string variable.
In the database it looks like:

Januari,
Oktober,
April,
November,
etc....

Since it's sorting alphabetical...how do i make it sort it sort in a correct way like: jan, feb, march, april, may....
My qvw file can be downloded from here:

http://www.joad.se/Test.qvw

Thanks,

[#7 · Accepted solution]

Try this code

MapMonth:
Mapping
LOAD * INLINE [
 Month, Order
 Januari, 1
 Februari, 2
 Mars, 3
 April, 4
 Maj, 5
 Juni, 6
 Juli, 7
 Augusti, 8
 September, 9
 Oktober, 10
 November, 11
 December, 12
];

Bilbokning:
LOAD ar,
 dag,
 Month(MakeDate(ar,ApplyMap('MapMonth',manad),dag)) as manad,
 nr;
SQL SELECT ar,
 dag,
 manad,
 nr
FROM bokuppgifter;

[qw_johan]

Thank you, thank you, thank you so much Fernando!
This is exactly what I was looking to do.

Just one more question...I want to understand the script.
What is going on here...
Month(MakeDate(ar,ApplyMap('MapMonth',manad),dag)) as manad,

Thanks again Fernando.

[#9]

Ok, is very simple.

The Function ApplyMap, replaces the information manad field by the number of month.

The Function MakeDate, creates a valid date, parameters (Year, Month, Day).

and finally, the Function Month, creates the months from date created.