
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Month number to Month name
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
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, there is. Please see my previous post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- « Previous Replies
- Next Replies »