topic month function in QlikView

month function

Sun, 12 Aug 2012 20:28:37 GMT

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.

Re: month function

swuehl — Sun, 12 Aug 2012 22:05:49 GMT

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?

Re: month function

Mon, 13 Aug 2012 05:45:47 GMT

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.

Re: month function

nilesh_gangurde — Mon, 13 Aug 2012 07:10:02 GMT

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

Re: month function

swuehl — Mon, 13 Aug 2012 08:56:07 GMT

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;