month function

[#1]

I am new to qlikview.... want to get month name in script but...

As per help month function writes back month name such as Jan,Feb... but what i found out if use it in script it returns month number.

[swuehl]

month() function returns a dual, so a text representation ('Jan',Feb',...) as well as a numerical representation (1,2,3...). You can force QV to return one or the other by using text() resp. num() function.

Could you describe you problem a bit closer, e.g by by posting a snippet of your load script?

[#3]

This is part of script

SQL SELECT CustomerID,

    EmployeeID,

    OrderDate,

    OrderID,

    Datename(month,OrderDate) As OrdMnthNm,

    month(OrderDate) As OrdMnth,

    year(OrderDate) AS OrdYear

FROM Northwind.dbo.Orders;

The Datename Sql function returns full month name but then sorting gives problem. The month qlikview function expected to return Jan,Feb but writes month number.

[nilesh_gangurde]

I will suggest you to write the script as follows,

TEMP:

SQL SELECT CustomerID,

    EmployeeID,

    OrderDate,

    OrderID

FROM Northwind.dbo.Orders;

DATA:
Laod *,

        Date(OrderDate) as Date,

        Day(OrderDate) as Day,

        Month(OrderDate) as Month,

        Year(OrderDate) as Year

Resident TEMP;

Drop table TEMP;

This Will work....

Regards,

Nilesh Gangurde

[swuehl]

joships123 schrieb:

This is part of script

SQL SELECT CustomerID,

    EmployeeID,

    OrderDate,

    OrderID,

    Datename(month,OrderDate) As OrdMnthNm,

    month(OrderDate) As OrdMnth,

    year(OrderDate) AS OrdYear

FROM Northwind.dbo.Orders;

The Datename Sql function returns full month name but then sorting gives problem. The month qlikview function expected to return Jan,Feb but writes month number.

Bottom line: If you call month() function in a SQL SELECT, you call the SQL function (as with datename), no QV functions are known within the SQL SELECT part of your script. You can use QV month() or any other QV functions only outside the SQL SELECT, e.g. in a preceding LOAD or a resident LOAD.

Preceding LOAD:

LOAD *,

    month(OrderDate) as OrdMnth;  // calling the QV month function

SQL SELECT CustomerID,

    EmployeeID,

    OrderDate,

    OrderID,

    Datename(month,OrderDate) As OrdMnthNm, // calling SQL function

    year(OrderDate) AS OrdYear                          // calling SQL function

FROM Northwind.dbo.Orders;