Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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
This always alphabetical, because this field as text and not numbers.
What is the format of the manad field in the database?
Be careful, month variable is a string call trim(manad)
In the script change
trim(manad) as manad
JJ
"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,
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;
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.
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.