topic Convert month number to a string in QlikView

Convert month number to a string

m4u — Tue, 02 Feb 2010 14:49:36 GMT

Hello

I want to display a pivoted report by month only, without the year part.

Assuming I have a transactions table with customer, date, sum fields - how can I do that? I want the columns on the top to show Jan,Feb,Mar and so on and not 1,2,3.

I know how to solve it on the sql level by doing

LEFT(DATENAME(MM,

H.DocDate),3)

but I am looking for some cleaner way of extracting the month name (without year, so MonthName is not good) out of the month number ( I have it anyway) or the full date

Convert month number to a string

Tue, 02 Feb 2010 14:54:46 GMT

you can do

Month(date) as xMonth;

in the load statement, and use xMonth Field

Convert month number to a string

m4u — Tue, 02 Feb 2010 15:30:15 GMT

I am loading from SQL Server so it gives me the month number..

Convert month number to a string

Tue, 02 Feb 2010 15:47:33 GMT

yes

Month(month_no)

will convert it to Jan, Feb, ......

Convert month number to a string

Tue, 02 Feb 2010 15:47:52 GMT

Hello,

Maybe you can try something like this:

Month(MakeDate(2009, monthField))

The "2009" is just so the MakeDate can work.

Steve

Convert month number to a string

msteedle — Tue, 02 Feb 2010 15:49:20 GMT

Use a preceding load and the QlikView month() function with your date field, e.g.


Calendar:
LOAD TransactionDate,
month(TransactionDate) as Month;
SQL SELECT TransactionDate
FROM SQLCalendar;

Convert month number to a string

Miguel_Angel_Baeyens — Tue, 02 Feb 2010 16:32:06 GMT


M4U wrote:
I am loading from SQL Server so it gives me the month number.. <div></div>

Somethink like

LOAD ... SubField(MonthNames, ';', DateField) AS MonthStr ...

should work, having that MonthName is an environment variable you are already loading in your main tab of your load script. Hope this helps.