topic Month number to Month name in QlikView

Month number to Month name

Tue, 09 Jun 2009 01:22:47 GMT

I'm looking for a way to convert Month numbers to their three-letter names.

Currently, the Month field is loaded by Month(OrderDate) where OrderDate is a Date. However, inside QV, the months show up as numbers from 1-12 and I need them to say Jan, Feb, ...

So far on the forums I've only seen how to convert from the Names to the numbers but not back. Is there a way to do this?

Thanks

Month number to Month name

Tue, 09 Jun 2009 01:32:58 GMT

The basic solution is to use 'MMM' in the date function. You will need to adapt this, but here is an example:

Date( myField, 'MMM')

Or you could try something like this:

Date( myField & '/1/2001', 'MMM')

Month number to Month name

Anonymous — Tue, 09 Jun 2009 01:40:04 GMT

If the months show up as numbers from 1 to 12, you can use mapping:


MonthMap:
MAPPING LOAD * INLINE [
 Num, Month,
 1, Jan
 2, Feb
 ...
 12, Dec];
...
LOAD
 dual(applymap('MonthMap', Num), Num) as Month
...

Dual optional but helpful to sort properly.

Month number to Month name

Tue, 09 Jun 2009 02:43:26 GMT

My script currently looks like this:

SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/M/YY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO [Excel Files;DBQ=C:\...\Employee.xls];
SQL SELECT *
FROM `C:\...\Employee.xls`.`Employees$`;

ODBC CONNECT TO [Excel Files;DBQ=C:\...\Sales.xls];
SQL SELECT *
FROM `C:\...\Sales.xls`.`Customers$`;

SQL SELECT *, Month(OrderDate) AS Month, Year(OrderDate) AS Year
FROM `C:\...\Sales.xls`.`Orders$`;

Where would I put the mapping code?

Thanks,

Yiling

Month number to Month name

Anonymous — Tue, 09 Jun 2009 03:43:15 GMT

Yiling,
It could be done this way:


...
ODBC CONNECT TO [Excel Files;DBQ=C:\...\Sales.xls];
SQL SELECT *
FROM `C:\...\Sales.xls`.`Customers$`;
MonthMap:
MAPPING LOAD * INLINE [
 Num, Month
 1, Jan
 2, Feb
 ...
 12, Dec];
SQL SELECT *, Month(OrderDate) AS Month, Year(OrderDate) AS Year,
 dual(applymap('MonthMap', Num), Num) as MonthName
FROM `C:\...\Sales.xls`.`Orders$`;

Asuming that the Month number is the Num field.
But you have Month already... I don't understand now what the problem is.

Month number to Month name

Tue, 09 Jun 2009 03:48:49 GMT

Basically in my Sales.xls spreadsheet, I have a Table called Orders which has field OrderDate which is all dates in the format MM/DD/YYYY.

Since I needed Month and Year Fields, I used Month(OrderDate) to get the month values from OrderDate and stored them into the newly made Month field. However, this field only has the numbers 1-12 which match up to their respective months, but I would like the list table with Months to show the month names so that users can click on "Jan" instead of "1" to see the sales in January.

I'll try your method now.

Thanks,

Yiling

Month number to Month name

Wed, 10 Jun 2009 01:13:38 GMT

That didn't work. My dates are in the Orders table in the Sales.xls spreadsheet. There's no built in function that converts the Month numbers to Month names?

Month number to Month name

Wed, 10 Jun 2009 01:21:34 GMT

Yes, there is. Please see my previous post.

Month number to Month name

Wed, 10 Jun 2009 01:28:46 GMT

Where would I put that?

I did it as an expression within QV and I got 1 box that says Dec and the other 11 boxes all say Jan.

Month number to Month name

Wed, 10 Jun 2009 01:36:02 GMT

I've attached a working example that looks like this:

Month number to Month name

Anonymous — Wed, 10 Jun 2009 01:40:15 GMT

Yiling,
Now I understand even less. You're saying that you "used Month(OrderDate) to get the month values from OrderDate and stored them into the newly made Month field". But in this case your Month field is dual, that is the number and the text at the same time. The only thing you need to do is to check what is the Number properties of your list box.
See the atched Calendar example. There are two list boxes named Month on the right. They are the same Month field, th difference is only in Number properties.
If it doesn't help, could you please post your app (ot a part of it) to see waht's going on there.

Month number to Month name

Wed, 10 Jun 2009 02:29:32 GMT

Try using the "PICK" function.

PICK(month(OrderDate),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

The "PICK" function will convert 1 to Jan., 2 to Feb, etc.

rob

Month number to Month name

Anonymous — Wed, 10 Jun 2009 02:44:46 GMT

Rob,
Pick is fine, but isn't it simpler to use just month(OrderDate)? Or at least text(month(OrderDate))...

Month number to Month name

Wed, 10 Jun 2009 02:53:00 GMT

I think that might be part of the problem. My Month field isn't dual. In your example, the Month ListBox displays the names by default. It only shows the numbers when you click Override Document Settings under the Number properties and click on Integer. However, mine only shows the number.

Month number to Month name

Wed, 10 Jun 2009 03:03:21 GMT

I think there is quite a difference between the Month() function in Qlikview and the Month() function in SQL. If I directly use the function Month(OrderDate) as an expression and set that as the dimension, Months show up with names and can be changed to numbers. However, I think the Month() when I did Select *, Month(OrderDate) as Month in my load script only takes numbers?

I really don't know SQL so I may be wrong but this is just what I'm observing happen.

Month number to Month name

Wed, 10 Jun 2009 03:12:24 GMT

Eugene,

I think my problem is most similiar to your example. Your Month field as shown in your tablebox is also numbers. I don't need any chart or any object to display the Month names based on the numbers. I need the numbers to actually be the monthnames.

So basically, if you could make your table box with the Month field show Jan, Feb, Mar, etc, then it would solve my problem too.

Yiling

Month number to Month name

Anonymous — Wed, 10 Jun 2009 03:28:36 GMT

The problem is that you're trying to use QV functions in SQL. It doesn't wotk this way. (Well, i should've noticed in your example). You can use applymap, or pick, or any other QV function on the QV LOAD level. Even if you use the same month() as now but on the LOAD, it will work just fine.
LOAD
*,
month(OrderDate) as Month;
SQL SELECT
...

Month number to Month name

Wed, 10 Jun 2009 03:43:46 GMT

Wow. Such a simple fix. Thank you.

Month number to Month name

Anonymous — Wed, 10 Jun 2009 07:16:55 GMT

We should've resolved it long ago. I simply didn't notice that the month() function was in SQL...

Month number to Month name

johnpaul — Wed, 17 Jun 2009 13:08:17 GMT

I have tried: PICK(month(OrderDate),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

The problem is if the OrderDate isn't actually a date but a number (1 = Jan, 2 = Feb etc).

So, if you remove the month(OrderDate) it works OK.

I used: PICK(OrderDate,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

In order to sort this, if you are using a List Box, you need to sort by an expression equal to the numeric number