topic Sorting in QlikView

Sorting

qw_johan — Thu, 03 Feb 2011 18:54:57 GMT

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

Sorting

Miguel_Angel_Baeyens — Thu, 03 Feb 2011 19:13:28 GMT

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.

Sorting

qw_johan — Thu, 03 Feb 2011 19:31:42 GMT

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

Sorting

Thu, 03 Feb 2011 20:21:35 GMT

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

Sorting

Thu, 03 Feb 2011 20:59:43 GMT

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

In the script change

trim(manad) as manad

Sorting

qw_johan — Thu, 03 Feb 2011 22:34:17 GMT

"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,

Sorting

Thu, 03 Feb 2011 22:50:11 GMT

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;

Sorting

qw_johan — Thu, 03 Feb 2011 22:58:28 GMT

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.

Sorting

Thu, 03 Feb 2011 23:12:56 GMT

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.